Inserting multiple records and updating identity column in SQL

Question

i have to insert multiple records into a table and in the mean time needed to insert the identity column of the first table in to another table.Can i do it avoiding loop?

Edited

   i have two tables named StudentMaster and StudentSujects.

  First Table structure is (StudentID int Identity(1,1),StudentName varchar(100))

  Second table structure is (SubjectID int Identity(1,1),StudentID int,SubjectName varchar(100)).

StudentID in the 'StudentSujects' table is the Identity column of first table 'StudentMaster'.

INSERT INTO StudentMaster
                            (
                                 StudentName
                            )
                    SELECT       StudentName
                    FROM OPENXML(@hDoc,'/XML/Students')
                    WITH(    StudentName    varchar(100)    'StudentName')


   I am inserting multiple records in to the first table using the above query.I the mean time i have to insert the identity column of each row in to the second table.

Post the table structure, and expected data and what have you tried so far — Akhil
– Akhil, Commented Aug 17, 2013 at 8:01

marc_s · Accepted Answer · 2013-08-17 10:15:46Z

You can use the OUTPUT clause to output multiple columns/rows on an INSERT operation into a table variable.

Assuming your table that you're inserting into has an IDENTITY column called ID, you could have code something like this:

DECLARE @InsertedData TABLE (NewID INT, SomeOtherColumn.....)

INSERT INTO dbo.YourTable(Col1, Col2, ..., ColN)
OUTPUT INTO @InsertedData(NewID, SomeOtherColumn) Inserted.ID, Inserted.OtherColumn
VALUES(Val11, Val12, ..., Val1N),
      (Val21, Val22, ..., Val2N),
      ....
      (ValM1, ValM2, ..., ValMN)

Of course, you need to have something that allows you to identify which row in your second table to insert which value into - that's entirely dependent on your situation (and you didn't offer any explanation of that in your question).

But basically, using the OUTPUT clause, you can capture as much information as you need, including the newly assigned IDENTITY values, so that you can then do your second insert based on that information.

Collectives™ on Stack Overflow

Inserting multiple records and updating identity column in SQL

Edited

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edited

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related