Inserting batch of rows into two tables in SQL Server 2008

Question

I have a requirement to insert multiple rows into table1 and at the same time insert a row into table2 with a pkID from table1 and a value that comes from a SP parameter.

I created a stored procedure that performs a batch insert with a table valued parameter which contains the rows to be inserted into table1. But I have a problem with inserting the row into table2 with the corresponding Id (identity) from table1, along with parameter value that I have passed.

Is there anyone who implemented this, or what is the good solution for this?

CREATE PROCEDURE [dbo].[oSP_TV_Insert]
  @uID int
  ,@IsActive int
  ,@Type int -- i need to insert this in table 2
  ,@dTableGroup table1  READONLY -- this one is a table valued
AS

DECLARE @SQL varchar(2000)
DECLARE @table1Id int
 BEGIN

    INSERT INTO dbo.table1  
        (uID
        ,Name
        ,Contact
        ,Address
        ,City
        ,State
        ,Zip
        ,Phone
        ,Active)  
    SELECT 
        @uID  
        ,Name
        ,Contact
        ,Address
        ,City
        ,State
        ,Zip
        ,Phone
        ,Active
        ,@G_Active   
    FROM @dTableGroup
--the above query will perform batch insert using the records from dTableGroup which is table valued

SET @table1ID = SCOPE_IDENTITY()

-- this below will perform inserting records to table2 with every Id inserted in table1.

Insert into table2(@table1ID , @type)

Can you post the code and what you think is not working properly? If you have a problem with obtaining identity value of inserted record, `INSERT` has `OUTPUT` clause that will give you that value. — a1ex07, Sep 02 '11 at 13:32
If I have understood you correctly and you are on 2008 see [Using merge..output to get mapping between source.id and target.id](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id) — Martin Smith, Sep 02 '11 at 13:35

score 4 · Accepted Answer · answered Sep 02 '11 at 14:21

You need to temporarily store the inserted identity values and then create a second INSERT statement - using the OUTPUT clause.

Something like:

-- declare table variable to hold the ID's that are being inserted
DECLARE @InsertedIDs TABLE (ID INT)

-- insert values into table1 - output the inserted ID's into @InsertedIDs
INSERT INTO dbo.table1(ID, Name, Contact, Address, City, State, Zip, Phone, Active)  
     OUTPUT INSERTED.ID INTO @InsertedIDs
     SELECT 
         @ID, Name, Contact, Address, City, State, Zip, Phone, Active, @G_Active   
    FROM @dTableGroup

and then you can have your second INSERT statement:

INSERT INTO dbo.table2(Table1ID, Type)
    SELECT ID, @type FROM @InsertedIDs

See the MSDN docs on the OUTPUT clause for more details on what you can do with the OUTPUT clause - one of the most underused and most "unknown" features of SQL Server these days!

score 1 · Answer 2 · answered Sep 05 '11 at 22:42

Another approach using OUTPUT clause and only one statement for inserting data in both destination tables:

--Parameters
DECLARE @TableGroup TABLE
(
    Name NVARCHAR(100) NOT NULL
    ,Phone VARCHAR(10) NOT NULL
);
DECLARE @Type INT;
--End Of parameters

--Destination tables
DECLARE @FirstDestinationTable TABLE
(
    FirstDestinationTableID INT IDENTITY(1,1) PRIMARY KEY
    ,Name NVARCHAR(100) NOT NULL
    ,Phone VARCHAR(10) NOT NULL
);
DECLARE @SecondDestinationTable TABLE
(
    SecondDestinationTable INT IDENTITY(2,2) PRIMARY KEY
    ,FirstDestinationTableID INT NOT NULL
    ,[Type] INT NOT NULL
    ,CHECK([Type] > 0) 
);
--End of destination tables

--Test1
--initialization
INSERT  @TableGroup
VALUES  ('Bogdan SAHLEAN', '0721200300')
        ,('Ion Ionescu', '0211002003')
        ,('Vasile Vasilescu', '0745600800');
SET     @Type = 9;

--execution
INSERT  @SecondDestinationTable (FirstDestinationTableID, [Type])
SELECT  FirstINS.FirstDestinationTableID, @Type
FROM
(
INSERT  @FirstDestinationTable (Name, Phone)
OUTPUT  inserted.FirstDestinationTableID
SELECT  tg.Name, tg.Phone
FROM    @TableGroup tg
) FirstINS

--check records
SELECT  *
FROM    @FirstDestinationTable;
SELECT  *
FROM    @SecondDestinationTable;
--End of test1

--Test2
--initialization
DELETE  @TableGroup;
DELETE  @FirstDestinationTable;
DELETE  @SecondDestinationTable;

INSERT  @TableGroup
VALUES  ('Ion Ionescu', '0210000000')
        ,('Vasile Vasilescu', '0745000000');
SET     @Type = 0; --Wrong value

--execution
INSERT  @SecondDestinationTable (FirstDestinationTableID, [Type])
SELECT  FirstINS.FirstDestinationTableID, @Type
FROM
(
INSERT  @FirstDestinationTable (Name, Phone)
OUTPUT  inserted.FirstDestinationTableID
SELECT  tg.Name, tg.Phone
FROM    @TableGroup tg
) FirstINS

--check records
DECLARE @rc1 INT, @rc2 INT;
SELECT  *
FROM    @FirstDestinationTable;
SET     @rc1 = @@ROWCOUNT;
SELECT  *
FROM    @SecondDestinationTable;
SET     @rc2 = @@ROWCOUNT;
RAISERROR('[Test2 results] @FirstDestinationTable: %d rows; @@SecondDestinationTable: %d rows;',1,1,@rc1,@rc2);
--End of test1

+1 Composable DML is only available on SQL Server 2008 and has quite a few limitations however. — Martin Smith, Sep 05 '11 at 23:50

Bryan · Answer 3 · 2011-09-02T14:08:12.810

0

Since you need all inserted identity values, look at the output clause of the insert statement: http://msdn.microsoft.com/en-us/library/ms177564.aspx

edited Sep 02 '11 at 14:08

answered Sep 02 '11 at 13:34

Bryan

17,112
7
57
80

i think the scope_identity got here is the last one?what about the first one? – user335160 Sep 02 '11 at 13:57
What is the name of your identity column? It doesn't look like it is ID, since you're providing a value in the stored procedure insert statement. – Bryan Sep 02 '11 at 14:09

Inserting batch of rows into two tables in SQL Server 2008

3 Answers3