CODEWITHSUNDEEP
sql-server
this.hart
this.hart

Reputation: 328

How to get multiple scope_identity from table then insert to other table?

brothers, can you help me? Sample Query: 

     DECLARE @id_scope TABLE (ID_TBA_PK int)
     Insert into TABLE_A
         Select ID_TBA_PK,ID_LET_FK,NAME, ADDRESS FROM TABLE_A WHERE 
         ID_LET_FK=@ID_LET_FK 
         set @id_scope  = scope_identity()  --but must get multiple identity 
         -- because above insert multiple,ID_TBA_PK is autoincement.

then insert to table others:

    insert into TABLE_B
              select ID_TBA_FK=@id_scope , NAME, ADDRESS FROM TABLE_B
              WHERE ID_TBA_FK=@ID_TBA_FK 

    --(MULTIPLE INSERT TO TABLE_B)

Upvotes: 0

Views: 1118

Answers (1)

LoztInSpace
LoztInSpace

Reputation: 5697

Add the OUTPUT clause to your INSERT. Something like this:

INSERT TABLE_A(NAME, ADDRESS, etc.) OUTPUT Inserted.ID_TBA_PK into @id_scope Select ID_TBA_PK,ID_LET_FK,NAME, ADDRESS FROM TABLE_A WHERE ID_LET_FK=@ID_LET_FK

This line I am assuming ID_TBA_PK is your new identity

OUTPUT Inserted.ID_TBA_PK into @id_scope

so the general case is

INSERT [table] (columns)

OUTPUT INSERTED.[column] into [@other table]

SELECT columns from .....

Upvotes: 1

Related Questions