CODEWITHSUNDEEP
sql-servert-sql
Sam
Sam

Reputation: 30388

Multiple inserts with the ID coming from SCOPE_IDENTITY

The scenario I'm handling is this: I have a bunch of ID's in a temp table I create in memory. So it's like this:

DECLARE @tempIds AS TABLE
(
   Id INT NOT NULL
)

For each Id in the temp table, I need to make an INSERT into table A, grab the database assigned ID and enter both ID's into table B. Looks like this:

DECLARE @CommentsId INT
INSERT INTO TableA
(Comments)
VALUES
('Entering some comments here...')
SET @CommentsId = SCOPE_IDENTITY()

INSERT INTO TableB
(Id, CommentsId)
VALUES
(@Id, @CommentsId)

The @Id in the INSERT statement for table B is an Id coming from the tempIds table. So, essentially, I'm making an entry into table A for every Id in my temp table then make a corresponding entry into table B.

I'd like to do this without using a cursor. I've done this before but maybe my brain is too tired and can't recall the technique.

Upvotes: 0

Views: 1021

Answers (2)

ColdSolstice
ColdSolstice

Reputation: 465

OUTPUT clause is what you need.

Setup TableA & TableB:

--DROP TABLE TableA;
CREATE TABLE TableA
(
    Id INT NOT NULL IDENTITY(1,1),
    Comments VARCHAR(256)
);

--DROP TABLE TableB;
CREATE TABLE TableB
(
    Id INT NOT NULL,
    CommentID INT NOT NULL
);

Create and populate @tempIds (Assuming you can also populate this table with your Comments):

DECLARE @tempIds AS TABLE
(
  Id INT NOT NULL,
  Comments VARCHAR(256) NOT NULL
);


INSERT INTO @tempIds (Id, Comments)
VALUES
   (1, 'Hello'),
   (2, 'Hi'),
   (3, 'How Are you'),
   (10, 'Goodbye');

Because you want to OUTPUT a field that's not actually being inserted into TableA (@tempIds.Id), we have to disguise this INSERT as a MERGE:

MERGE TableA as [target]
USING
  (SELECT Id
         ,Comments
   FROM @tempIds
  ) AS [source]
ON 1=0 --Always evaluate to false since we're only doing INSERTS
WHEN NOT MATCHED BY TARGET THEN
   INSERT (Comments) VALUES ([source].[Comments])
OUTPUT [source].ID, INSERTED.[Id] --We can OUTPUT source.ID here in a MERGE where we could not in an INSERT
    INTO TableB ([Id],[CommentId]);

SELECT * FROM @tempIds
SELECT * FROM TableA
SELECT * FROM TableB

@tempIds:

Id          Comments
----------- --------
1           Hello
2           Hi
3           How Are you
10          Goodbye

TableA:

Id          Comments
----------- --------
1           Hello
2           Hi
3           How Are you
4           Goodbye

TableB:

Id          CommentID
----------- -----------
1           1
2           2
3           3
10          4

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

use the OUTPUT clause and output the identity into a table variable

declare @out table (Id int, comments varchar(100))

INSERT INTO TableA (Comments) 
    OUTPUT  inserted.* 
    INTO    @out
SELECT  Id
from    @tempIds

Upvotes: 0

Related Questions