SQL Server 2012 Insert a Set and Get all PK Values?

Question

I have a sproc that takes a TVP, the input has 6,000 rows. The current code copies the TVP into a temp table, iterates RBAR over the temp table to insert a row on a real table, gets the Identity/PK value from the insert, updates the temp table with the PK (used later in the stored procedure), and repeats.

Is there a cool and/or quick way to insert the whole temp table and then update it after that with the PK values? Nothing straightforward is coming to mind, all ideas appreciated.

I think I can add an additional column to the temp table and put a sequence number on it so the rows are all unique and follow this idea: http://www.sqlteam.com/article/using-the-output-clause-to-capture-identity-values-on-multi-row-inserts but I'm open to other set-based suggestions....

Thanks.

Answer 1

I would suggest that you bulk insert from your TVP into your table, using the OUTPUT clause to populate a temporary table with your results.

Here's an example:

-- Setup
-- Create the data type
CREATE TYPE dbo.TVPtest AS TABLE 

(

    c1 int NOT NULL, 
    c2 int NULL 
)
GO
CREATE TABLE tableA
(
    id int primary key identity,
    c1 int not null,
    c2 int null
)

-- This is the example
declare @t TVPTest
declare @i int = 1000

-- get a TVP with 1000 rows
WHILE @i >= 0
BEGIN
    INSERT INTO @t
        VALUES (@i, ABS(CHECKSUM(NewId())) % 99)
    SET @i= @i -1
END

-- logic from here is what you would put in your stored procedure
CREATE TABLE #new
(
    id int,
    c1 int not null,
    c2 int null
)

INSERT INTO tableA (c1, c2)
OUTPUT inserted.id, inserted.c1, inserted.c2 into #new
SELECT c1, c2
FROM @t

SELECT *
FROM #new