Inserting batch of rows into two tables in SQL Server 2008

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 a开发者_运维技巧nd 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)

---

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!

---

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

---

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