Inserting to one table, insert the ID to second ta

Question

Is it possible to populate a second table when I insert into the first table?

Insert post to table1 -> table 2 column recieves table1 post's unique id.

What I got so far, am I on the right track?

    CONSTRAINT [FK_dbo.Statistics_dbo.News_News_NewsID] FOREIGN KEY ([News_NewsID]) REFERENCES [dbo].[News] ([NewsID])

Answer 1

Yes it is, it sounds like you want a SQL Trigger, this would allow you to trigger logic based on actions on one table, to perform other actions in the DB. Here's another article on creating Simple SQL Triggers

SQL Server 2008 - Help writing simple INSERT Trigger

A Word of caution, this will do all the logic of updating the new table, outside of any C# code you write, it might sound nice to not have to manage it upfront, but you also lose control over when and if it happens.

So if you need to do something different later, now you have to update your regular code, as well as the trigger code. This type of logic can definitely grow, in large systems, and become a nightmare to maintain. Consider this, the alternative would be to build a method that adds the id to the new table after it inserts into the first table.

While i don't know what you're using to do your inserts assuming it's a SQL Command you can get back the ID on an identity column from the insert using Scope_Identity, found here

How to insert a record and return the newly created ID using a single SqlCommand?

if it's EF or some other ORM tool, they should either automatically update the entity, or have other mechanisms to deliver this data.

Answer 2

The only thing I can think of is that you can use a trigger to accomplish this. There is nothing "built in" to SQL Server that would do it. Why not just do it from your .NET code?

Answer 3

Lots of ways:

1. an insert trigger
2. read SCOPE_IDENTITY() after the first insert, and use it to do a second
3. use the output clause to do an insert

Examples:

1:

create trigger Foo_Insert on Foo after insert
as
begin
    set nocount on
    insert Bar(fooid)
    select id from inserted
end
go
insert Foo (Name)
values ('abc');

2:

insert Foo (Name)
values ('abc');
declare @id int = SCOPE_IDENTITY();

insert Bar(fooid)
select @id

3:

insert Bar(fooid)
select id from (
    insert Foo (Name)
    output inserted.id
    values ('abc')) x