CODEWITHSUNDEEP
sqlsql-serverselectinsertoutput
user2571510
user2571510

Reputation: 11387

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

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

Upvotes: 0

Views: 72

Answers (2)

nshah
nshah

Reputation: 340

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

Upvotes: 1

krivtom
krivtom

Reputation: 24916

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)

Upvotes: 1

Related Questions