Copy a primary key from a target table as a foreign key on the source table during an insert

Question

The Situation:
I am inserting information from one table to another, a source and target. When the information is inserted into the target, a primary key is created. (In this case it is an integer.) I then need to be able to tie back to the source table. However, based on the data being moved, I am not able to reliably get the 1:1 match between the target and source tables.

The Question:
Is there a way to copy the primary key that was created for record(x) in the target table and copy it as a foreign key to that same record(x) in the source table as the bulk insert is happening?

Details:
I am trying to get this done in SQL. I have a work-around to this problem but I figure there has to be a way to do what I'm asking.

I don't believe so, the target table is locked down pretty tight. — Jonathan, Feb 06 '13 at 18:37
It is a lot easier to add the PK of the source to the target table. Are you able to add a (temporary) extra column to the target? — Jacco, Feb 06 '13 at 18:41
@Jacco - This was the workaround that I used. The reason I was looking into the solution I asked about, is that if every developer inserting to the same target table did that, we would have alot of extra columns and that table would get very large. I figured there was a better practice. — Jonathan, Feb 06 '13 at 18:43
True, it would be a lot better, but I'm not sure that can be done. You could add the column, finish your insert, update the FK in the source and then remove the extra column. I hope you'll get a nicer approach in the answers. — Jacco, Feb 06 '13 at 18:49
@Jacco - Either way I appreciate your insight. That may prove to be the best way in the end. — Jonathan, Feb 06 '13 at 18:57
How many rows are you inserting? Clearly you could get there with a cursor. With output the problem is no place for the PK. But I think you could just have one column SharedFK. With an update lock you are not going to get a value from another update. The other update would not even yet see the record. — paparazzo, Feb 06 '13 at 20:17
If this is SQL Server 2008+, try the method discussed in [this question](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id "Using merge..output to get mapping between source.id and target.id"). — Andriy M, Feb 06 '13 at 21:48
@Andriy M - Thanks for this link, it got me to the answer i needed. If you write it as an answer I can vote it as the correct one. Thanks again. — Jonathan, Feb 07 '13 at 20:20
It might actually be better to link your answer to that one by closing yours as a duplicate. If you are unaware of this closing thing, please don't worry. This kind of linking makes it easier for others to find a solution (meaning you've still been helpful with your question). I'm tagging your question as `sql-server-2008` and adding my close vote. If you disagree that your question is a duplicate, please clarify that either in a comment or (probably, better) in the question. — Andriy M, Feb 08 '13 at 06:10

Jonathan · Accepted Answer · 2013-02-07T21:22:02.640

I found my answer after reading this great article.

http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge.aspx

I acheived what I was looking for by using a MERGE and its OUTPUT clause. Here is my sample code that I used to figure this out.

I started by creating 3 temporary tables, #Temp2, #Temp3 and #Temp4. #Temp2 is considered the source table. #Temp3 would be the target table and #Temp4 is a bridge. I then inserted a few rows of very simple data, in this case just one field - Value.

CREATE TABLE #Temp2(
OldID INT IDENTITY(1,1),
Value INT,
NewFK INT)

CREATE TABLE #Temp3(
NewerID INT IDENTITY(1,1),
Value INT)

CREATE TABLE #Temp4(
OldID INT NOT NULL,
NewerID INT NOT NULL,
Value INT)

INSERT INTO #Temp2(Value)
VALUES(30), (40), (50), (70)

INSERT INTO #Temp3(Value)
VALUES (333), (444), (555), (777)

Then comes the MERGE statement that does the dirty work. It will be taking the value from #Temp2 and putting it into #Temp3. It will then take the ID created in #Temp3, the ID from #Temp2 and the Value that was passed, and throw them all into #Temp4.

MERGE INTO #Temp3 AS tgt
USING #Temp2 AS src
ON 1=0
WHEN NOT MATCHED THEN
    INSERT(
    Value)
    VALUES(
    src.Value)
OUTPUT
    src.OldID,
    INSERTED.NewerID,
    src.Value
INTO #Temp4(OldID, NewerID, Value);

Then I ran an UPDATE to the staging table #Temp2 to update the NewFK field with the new ID. Lastly, do a simple SELECT to see the updated information.

UPDATE X
SET X.NewFK = Z.NewerID
FROM #Temp2 X
JOIN #Temp4 Z
ON X.OldID = Z.OldID

SELECT * FROM #Temp2

This acheived exactly what I needed and is a pretty streamlined way of doing things. I hope this will help some people who come across this question. Thanks everyone for your insight and responses.

NOTE: I believe MERGE was introduced in SQL Server 2008.

Jonathan

Chains · Answer 2 · 2013-02-06T20:45:15.783

0

One approach would be to set identity insert for your target table to 'on' (http://msdn.microsoft.com/en-us/library/ms188059.aspx). Then make that identity part of your 'source' data before you run the insert. Just remember to turn identity insert back off again once you're done.

EDIT
Not sure what your situation is, but one apporach I've taken in the past is to create a field to hold 'external source ID', just in case I needed to refer back to the source at some point in the future. In my case, this was for reference only, not normal transactional use.

edited Feb 06 '13 at 20:45

answered Feb 06 '13 at 18:37

Chains

12,541
8
45
62

How is the source supposed to know the identity of the target. Other inserts could be taking place. – paparazzo Feb 06 '13 at 20:12
Lots of ways. One would be to start with a number significantly higher (e.g., if his target table was up to ID=20,000, and he expects 1000 inserts per day, then start with a number high enough to account for that.) – Chains Feb 06 '13 at 20:42
OP stated every developer. Just how do developers not collide? – paparazzo Feb 06 '13 at 21:41
Every developer could query the max(id), and then set the SEED value on the target table. But honestly, is OP really going to let a lot of developers besides the dba run bulk copies & inserts? I'd be **nervous** to open a door where all of my integrity checks could be bypassed. – Chains Feb 06 '13 at 22:09
Op stated multiple developers so it obviously is a need. Does NOT work! Dev A resets seed and before he is done Dev B is gets max(id) - it will be IN the Dev A range. Really along from not working table is locked down pretty tight and your answer is give every deveoper authority to reset the seed. – paparazzo Feb 06 '13 at 23:02
You're making a lot of assumptions there about how the work flow would go. And just to be clear, I do NOT think this is an ideal solution in general terms -- but if the OP wants to use bulk insert, then the traffic is going to be one-way, in which case something's going to have to give. – Chains Feb 07 '13 at 19:45
"Inserting information from one table to another." The tool Bulk Insert is only used for loading from data file. And even if it was from file if Dev A resets seed and before he is done Dev B gets max(id) - it will be IN the Dev A range. Comments from OP indicates he is not referring to Bulk Insert the tool. – paparazzo Feb 07 '13 at 21:15
He specifically said "bulk insert" in the title. And yes, yes, multiple devs resetting each others seeds -- I get that -- which is why I said it wasn't ideal in general terms, and also that you were making assumptions about work flow that the OP hadn't specificied (such as, that both devs would be unaware of / independent of each other for starters). Tell you what -- let's compare our scores on our two answers here and see which one is best... :-) – Chains Feb 07 '13 at 21:25
Tie on score but the answer the OP posted is much closer to mine. Cheers ;) – paparazzo Feb 07 '13 at 21:44

score 0 · Answer 3 · answered Feb 06 '13 at 20:52

If you can get a SharedExtPK in the target then this should work.
In this case logID is the PK of the source.
Tested:

DECLARE @MyTableVar table(
  TargetPK     int NOT NULL,
  SourcePK     int NOT NULL
  );

INSERT INTO IdenOutPut (someValue, sharedExtKey)
  OUTPUT INSERTED.iden, 
         INSERTED.sharedExtKey
  INTO @MyTableVar
    SELECT name, logID
    FROM CatID

update sPK 
set sPK.ExtPK = tTbl.TargetPK
FROM @MyTableVar as tTbl 
JOIN CatID as sPK 
  on sPK.logID = tTbl.SourcePK 
GO

If the values you insert are unique then could use that.
But it would get trickier.

I thought 'bulk insert' was the tool OP wants to use... – Chains Feb 07 '13 at 19:46 — Chains, Feb 07 '13 at 19:46

Copy a primary key from a target table as a foreign key on the source table during an insert

3 Answers3