How to create int IDs during set insert in SQL Server 2008

Question

I am exploring the use of table valued parameters in stored procedures to do multiple inserts in a single call to the database.

The table value parameter contains information that, more or less, reflects the definition of the table I want to insert into. It is only missing the ID column.

If I have the following table definition:

Create Table Product
(
     ProductID int,
     ProductName varchar(100),
     ProductDesc varchar(4000),
     ProductCost int
)

Type definition:

Create Type ProductTable as Table
(
    ProductName varchar(100),
    ProductDesc varchar(4000),
    ProductCost int
)

and stored procedure definition:

Create Procedure usp_Product_Insert
    @Products ProductTable READONLY
AS
    INSERT Product
    (
        ProductID,
        ProductName,
        ProductDesc,
        ProductCost
    )
    SELECT
        (Select ISNULL(Max(ProductID),0) + 1 From Product),
        P.ProductName,
        P.ProductDesc,
        P.ProductCost
    FROM
        @Products P

How do I alter what I have to insert unique id's if there is more than one set of data in my parameter?

Right now, if I run the following statement

Truncate Table Product
Declare @Products ProductTable
Insert @Products Values ('Playstation', 'Game Console', 300)
exec usp_Product_InsertUpdate_WithOutput @Products

through the stored procedure with no previous records in the table, I get

ProductID   ProductName ProductDesc ProductCost
1           Playstation     Game Console    300

But if I run more than one record through

Truncate Table Product
Declare @Products ProductTable
Insert @Products Values
('Playstation', 'Game Console', 300),
('Xbox', 'Game Console', 200),
('Wii', 'Game Console', 150)
exec usp_Product_InsertUpdate_WithOutput @Products

I get

ProductID   ProductDesc     ProductDesc ProductCost
1           Playstation     Game Console    300
1           Xbox            Game Console    200
1           Wii             Game Console    150

What I would like to see is

ProductID   ProductDesc     ProductDesc ProductCost
1           Playstation     Game Console    300
2           Xbox            Game Console    200
3           Wii             Game Console    150

How can I accomplish this efficiently without using identity?

Using `IDENTITY` is the **best and only reliable way** to do this. Why are you explicitly excluding this approach? — marc_s, Mar 14 '13 at 18:28
@DeveloperKlin That's not a Business Requirement since a **business** requirement would not specify what you can use in the database. There is no **business** need to use a specific data type, there is a **developer** somewhere between the DB and the business that is interpreting something. — JNK, Mar 14 '13 at 18:35
You are right, I misused the term. Regardless, these are the guidelines I have to work with. I am obviously aware of the benefits of identity — scottmgerstl, Mar 14 '13 at 18:35
You can't accomplish this efficiently without using identity. — Dominic Goulet, Mar 14 '13 at 18:48

score 4 · Accepted Answer · answered Mar 14 '13 at 18:50

You can't. And should not even try.

Business define business keys.

Developers define surrogate keys.

What you want is a surrogate key, so use an IDENTITY column.

If your business defines surrogate keys, then your duty is to teach them they should not do so.

score 0 · Answer 2 · edited May 23 '17 at 11:33

0

After further searching, I found this approach provided by hims056:

The stored procedure would be rewritten as follows:

Create Procedure usp_Product_Insert
    @Products ProductTable READONLY
AS
    INSERT Product
    (
        ProductID,
        ProductName,
        ProductDesc,
        ProductCost
    )
    SELECT
        (ROW_NUMBER( ) OVER ( ORDER BY ProductID ASC )) + (Select ISNULL(Max(ProductID),0) From Product),
        P.ProductName,
        P.ProductDesc,
        P.ProductCost
    FROM
        @Products P

edited May 23 '17 at 11:33

Community

1
1

answered Mar 14 '13 at 18:34

scottmgerstl

765
8
18

2

This provides no concurrency... which is exactly why you should use an identity column instead of rolling your own solution and getting it wrong. – Michael Fredrickson Mar 14 '13 at 18:45

How to create int IDs during set insert in SQL Server 2008

2 Answers2