CODEWITHSUNDEEP
sql-serverinsert
Dmitry S
Dmitry S

Reputation: 33

Perform insert for each row taken from a select with SCOPE_IDENTITY()

I need to insert rows in Table1 for each row in Table2.

Also I need to insert for each new inserted row in Table1 a new row in Table3 with inserted record Id.

My script is as follows:

DECLARE @Id INT;

BEGIN TRAN

BEGIN TRY

INSERT INTO dbo.Table1(UN_ID, AT_ID, AA_SUM, AA_START_DATE, AA_END_DATE, WR_ID) 
SELECT 
    116,        
    3,          
    100,        
    '20170501', 
    '20170601', 
    WR_ID       
FROM Table2


SELECT @Id = SCOPE_IDENTITY()
INSERT INTO dbo.Table3 VALUES (@Id, 10)

COMMIT TRAN

END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH

But this script inserts only one row in Table3.

How can I insert in Table3 for each new inserted row in Table1?

EDIT: Table1 has auto-incremented primary key ID. This ID should be inserted into Table3.

Upvotes: 0

Views: 1118

Answers (1)

Coder1991
Coder1991

Reputation: 735

This can be done using output clause as below and before that declare a table variable having the same structure as table 3.

declare @tbl3 table(id int)

INSERT INTO dbo.Table1(UN_ID, AT_ID, AA_SUM, AA_START_DATE, AA_END_DATE, WR_ID)
output Inserted.ID into @tbl3
SELECT 
    116,        
    3,          
    100,        
    '20170501', 
    '20170601', 
    WR_ID       
FROM Table2

insert into dbo.table3 
select id,10 from @tbl3

Upvotes: 1

Related Questions