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 ano开发者_开发知识库ther 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

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

If this were SQL Server 2005 or later I would suggest using the output clause in your insert statement to retrieve the ids just inserted, but that was not available in SQL Server 2000.

If your data contains some column or series of columns which is unique other than the identity column, then you can query your first table based on that series of columns to get the ids and use that to populate your second table.

If the target tables were empty you could use SET IDENTITY_INSERT ON - this would allow to insert original values to identity columns, and you will not have to update referenced IDs. Of course if there is any existing ids that can overlap inserted ids - that is not the solution.

If names in first tables are unique, you could boild mapping between new and old ids and perform update something like this:

UPDATE S
SET S.id = F.id
FROM second S
INNER JOIN first_original FO ON FO.id = S.id
INNER JOIN first F ON F.name = FO.name

If names are not unique, then original ids should be saved in "first" in order to provide mapping between old and new ids. It can be temporary new column that can be deleted after ids in "second" will be updated.

Or as Rich Andrews said you could use @scope_identity, but in this case you will have to perform insert one by one - declare a cursor on source table, insert each record, get its new id and insert it into "second" table.