1

I am doing an insert using tvp of large amount of products. After insert I need IDs of these products. Is there any safe way to get them straight away after stored procedure insert?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
kali
  • 109
  • 1
  • 2
  • 9
  • You can do by joining your TVP and original table with unique column and return the column(s) you required by using SELECT statement. – SelvaS Mar 25 '15 at 07:31
  • In the other tvp insert where I know its safe I am just selecting newest objects. But in this case it is possible to do couple of inserts into db at once. I was thinking about technique from second answer here: http://stackoverflow.com/questions/8022682/how-can-i-use-a-table-valued-parameter-to-insert-multiple-rows-and-then-return-t but I am not sure if its safe – kali Mar 25 '15 at 07:33
  • @kali Do you have any unique column for each product other than ID? – SelvaS Mar 25 '15 at 07:47
  • @SelvaTS no, ID is the only one. – kali Mar 25 '15 at 07:50

2 Answers2

3

I assume you want to return inserted IDs. You can use OUTPUT clause and magic table inserted to return what you need.

CREATE TYPE product_type AS TABLE(name VARCHAR(100), barcode INT);

CREATE TABLE products(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
                      name VARCHAR(100), barcode INT);

CREATE PROCEDURE dbo.my_insert_product
   @products product_type READONLY
AS
BEGIN
   INSERT INTO products(name, barcode)
   OUTPUT inserted.ID, inserted.name   -- add columns you need
   SELECT name, barcode
   FROM @products;
END;

Calling:

DECLARE @t AS product_type;

INSERT INTO @t
VALUES ('Product1', 1), ('Product2', 2), ('Product3', 2),
       ('Product4', 3), ('Product5', 4), ('Product6', 5);

EXEC dbo.my_insert_product @t;

SqlFiddleDemo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

With MERGE you can output values from the TVP which (without any good reason) is not possible with INSERT. You could add a unique key to the TVP that allows your app to understand what rows received what ID.

usr
  • 168,620
  • 35
  • 240
  • 369