CODEWITHSUNDEEP
sql-server
Alexander
Alexander

Reputation: 20224

SQL Script add records with identity FK

I am trying to create an SQL script to insert a new row and use that row's identity column as an FK when inserting into another table.

This is what I use for a one-to-one relationship:

INSERT INTO userTable(name) VALUES(N'admin')
INSERT INTO adminsTable(userId,permissions) SELECT userId,255 FROM userTable WHERE name=N'admin'

But now I also have a one-to-many relationship, and I asked myself whether I can use less SELECT queries than this:

INSERT INTO bonusCodeTypes(name) VALUES(N'1500 pages')
INSERT INTO bonusCodeInstances(codeType,codeNo,isRedeemed) SELECT name,N'123456',0 FROM bonusCodeTypes WHERE name=N'1500 pages'
INSERT INTO bonusCodeInstances(codeType,codeNo,isRedeemed) SELECT name,N'012345',0 FROM bonusCodeTypes WHERE name=N'1500 pages'

I could also use sth like this:

INSERT INTO bonusCodeInstances(codeType,codeNo,isRedeemed) 
    SELECT name,bonusCode,0 FROM bonusCodeTypes JOIN 
    (SELECT N'123456' AS bonusCode UNION SELECT N'012345' AS bonusCode)
    WHERE name=N'1500 pages'

but this is also a very complicated way of inserting all the codes, I don't know whether it is even faster.

So, is there a possibility to use a variable inside SQL statements? Like

var lastinsertID = INSERT INTO bonusCodeTypes(name) OUTPUT inserted.id VALUES(N'300 pages') 
INSERT INTO bonusCodeInstances(codeType,codeNo,isRedeemed) VALUES(lastinsertID,N'123456',0)

Upvotes: 0

Views: 50

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28769

OUTPUT can only insert into a table. If you're only inserting a single record, it's much more convenient to use SCOPE_IDENTITY(), which holds the value of the most recently inserted identity value. If you need a range of values, one technique is to OUTPUT all the identity values into a temp table or table variable along with the business keys, and join on that -- but provided the table you are inserting into has an index on those keys (and why shouldn't it) this buys you nothing over simply joining the base table in a transaction, other than lots more I/O.

So, in your example:

INSERT INTO bonusCodeTypes(name) VALUES(N'300 pages');
DECLARE @lastInsertID INT = SCOPE_IDENTITY();
INSERT INTO bonusCodeInstances(codeType,codeNo,isRedeemed) VALUES (@lastInsertID, N'123456',0);
SELECT @lastInsertID AS id;  -- if you want to return the value to the client, as OUTPUT implies

Instead of VALUES, you can of course join on a table instead, provided you need the same @lastInsertID value everywhere.

As to your original question, yes, you can also assign variables from statements -- but not with OUTPUT. However, SELECT @x = TOP(1) something FROM table is perfectly OK.

Upvotes: 1

Related Questions