SQL Server: How to use result from one INSERT for another INSERT

Question

I have a stored procedure that is meant to update two tables at once.

My problem here is that the first table has an auto-incrementing ID column ("commentID") and my second table has a relationship on this so I need the newly created ID from the first INSERT in order to make the second INSERT.

I tried the following which I can save without errors but it doesnt execute as it should and does not update the tables as intended. Can someone tell me what I am doing wrong here ?

My SQL:

ALTER PROCEDURE [dbo].[MOC_UpdateComment] @imgID int, @commentID int = '999999', @comment nvarchar(1000), @lastUpdate nvarchar(50), @modBy varchar(50) AS BEGIN DECLARE @temp AS TABLE ( commentID int ) SET NOCOUNT ON; BEGIN TRANSACTION; INSERT INTO MOC_BlogComments ( imgID, comment ) OUTPUT inserted.commentID INTO @temp(commentID) SELECT @imgID, @comment INSERT INTO MOC_LogComments ( commentID, lastUpdate, modTime, modBy ) SELECT commentID, @lastUpdate, GETDATE(), @modBy FROM @temp COMMIT TRANSACTION; END

It seems you are attempting to INSERT the identity value inappropriately. Comment out the `imgID,** in the first insert, and the rest shold now be fine. — Pieter Geerkens
– Pieter Geerkens, Commented Aug 26, 2014 at 17:49

nshah · Accepted Answer · 2014-08-26 18:27:32Z

DECLARE @imgID INT, @commentID INT = '999999', @comment NVARCHAR(1000), @lastUpdate NVARCHAR(50), @modBy VARCHAR(50) DECLARE @MORC_BlogComments AS TABLE ( id INT IDENTITY(1, 1) NOT NULL, imgid INT, comment VARCHAR(100) ) DECLARE @MORC_LogComments AS TABLE ( commentid INT, lastupdate DATETIME, modtime DATETIME, modby VARCHAR(100) ) DECLARE @TEMP AS TABLE ( commentid INT ) SET nocount ON; BEGIN TRANSACTION; INSERT INTO @MORC_BlogComments (imgid, comment) output inserted.id INTO @TEMP(commentid) VALUES (@imgID, @comment) INSERT INTO @MORC_LogComments (commentid, lastupdate, modtime, modby) SELECT commentid, @lastUpdate, Getdate(), @modBy FROM @temp SELECT * FROM @MORC_LogComments

krivtom · Accepted Answer · 2014-08-26 17:15:09Z

Function SCOPE_IDENTITY() returns the identity of last insert operation. You can use it to get the value which you need to use in second INSERT statement

You can use it like this in your statement:

INSERT INTO MORC_BlogComments (imgID, comment) VALUES (@imgID, @comment) INSERT INTO MORC_LogComments (commentID, lastUpdate, modTime, modBy) VALUES (SCOPE_IDENTITY(), @lastUpdate, GETDATE(), @modBy)

Thanks a lot for this ! Where would I implement this in the above ? Also, can you tell me what is wrong with the above as I thought OUTPUT is an option here as well ?
OUTPUT is actually a better option, since in some circumstances SCOPE_IDENTITY() can provide the incorrect result. What is going wrong with your 2nd (I'm assuming) insert?

Collectives™ on Stack Overflow

SQL Server: How to use result from one INSERT for another INSERT

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related