Search code examples
t-sqlforeign-keysidentityidentity-insert

Insert into table with Identity and foreign key columns

I was trying to insert values from one table to another from two different databases.

My issue is I have two tables with a relation and the first table is having an identity column also.

eg table first(id, Name) - table second(id, address)

So now both the table exist with values in a db and i am trying to copy values from this db to another db.

So when I insert values from first db to second db the the first table will insert values for the Id column by itself so now I have to link that id to the second table.

How can I do that?

UPDATE using MSSQL server 2000

Solution

  • You can use @scope_identity immediately after your insert in SQL server 2000 which will give you the last id within the current scope but I'm not sure how that would work with bulk inserting of data

    http://msdn.microsoft.com/en-us/library/ms190315.aspx