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.

@Rahul, are you saying a trigger on the temp table? On the real table? How would that move the approach from row-based to set-based? — Snowy, May 28 '14 at 03:34
you are essentially doing a bulk insert into real table and for each insert updating temp table. so I would do like, `insert into real_table select from TVP` -> define a after insert trigger for each row update the temp table accordingly. At least I see you are going by cursory approach. — Rahul, May 28 '14 at 03:38
You need a primary key in the temp table and you can use merge with output to create table that maps the PK from the temp table to the generated identity. 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) — Mikael Eriksson, May 28 '14 at 06:08

score 0 · Answer 1 · answered May 28 '14 at 08:23

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

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

1 Answers1