title: How to create int IDs during set insert in SQL Server 2008
body: <p>I am exploring the use of table valued parameters in  stored procedures to do multiple inserts in a single call to the database.</p>
  
  <p>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. </p>
  
  <p>If I have the following table definition:</p>
  
  <pre><code>Create Table Product
  (
       ProductID int,
       ProductName varchar(100),
       ProductDesc varchar(4000),
       ProductCost int
  )
  </code></pre>
  
  <p>Type definition:</p>
  
  <pre><code>Create Type ProductTable as Table
  (
      ProductName varchar(100),
      ProductDesc varchar(4000),
      ProductCost int
  )
  </code></pre>
  
  <p>and stored procedure definition:</p>
  
  <pre><code>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
  </code></pre>
  
  <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?</p>
  
  <p>Right now, if I run the following statement</p>
  
  <pre><code>Truncate Table Product
  Declare @Products ProductTable
  Insert @Products Values ('Playstation', 'Game Console', 300)
  exec usp_Product_InsertUpdate_WithOutput @Products
  </code></pre>
  
  <p>through the stored procedure with no previous records in the table, I get</p>
  
  <pre><code>ProductID   ProductName ProductDesc ProductCost
  1           Playstation     Game Console    300
  </code></pre>
  
  <p>But if I run more than one record through</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I get</p>
  
  <pre><code>ProductID   ProductDesc     ProductDesc ProductCost
  1           Playstation     Game Console    300
  1           Xbox            Game Console    200
  1           Wii             Game Console    150
  </code></pre>
  
  <p>What I would like to see is </p>
  
  <pre><code>ProductID   ProductDesc     ProductDesc ProductCost
  1           Playstation     Game Console    300
  2           Xbox            Game Console    200
  3           Wii             Game Console    150
  </code></pre>
  
  <p><strong>How can I accomplish this efficiently without using identity?</strong></p>