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:
Post a Comment