Tuesday, September 27, 2011

Using OUTPUT to set up foreign keys' data


So, I was looking at a process that inserts records into table A (one at a time), then uses SCOPE_IDENTITY() from each insert to get the ID and uses that for the insert into table B, in order to provide a cross-reference tabled (foreign key).  

Obviously, there's a better way - OUTPUT, usable since SQL Server 2005.

An example.  We populate “blah”, then use the values from that to insert into “blah2”, with a foreign key ID of the first table.  

CREATE TABLE blah (id INT IDENTITY, logon_name VARCHAR(50))
CREATE TABLE blah2 (id INT IDENTITY, blahID int, dn VARCHAR(200))

INSERT INTO blah
      SELECT TOP 10 name FROM sysusers
INSERT INTO blah
      SELECT TOP 10 name FROM sysusers
DELETE FROM blah
--incrementing to show different ID values.

DECLARE @insertedlist TABLE (id int, the_logonname VARCHAR(50))

INSERT INTO blah
output inserted.id, inserted.logon_name INTO @insertedlist
SELECT TOP 10 name
FROM sysusers ORDER BY createdate

INSERT INTO blah2 (blahid, dn)
SELECT insertedlist.id, sysusers.uid
FROM @insertedlist insertedlist INNER JOIN sysusers
ON insertedlist.the_logonname = sysusers.name

SELECT * FROM blah
SELECT * FROM blah2

No comments: