title: How can I insert autoincrement value using SQL insert into select statement
body: <p>I have the following code to insert data from a table in another database, but how can I insert into a primary key column by incrementing the last record in ID column value by 1? In my case <code>[WORK ORDER #]</code> is a primary key it doesn't allow null.</p>
  
  <p><code>[WORK ORDER #]</code> is <code>nvarchar(10)</code></p>
  
  <pre><code>INSERT INTO DB1.dbo.WORKORDERS ([WORK ORDER #], [CUSTOMER], [SO DATE], [SO NUMBER])
      SELECT * 
      FROM OPENQUERY([DB29],
                    'SELECT DISTINCT 
                         NULL, --need to set auto increment value here 
                         Customers.Customer_Bill_Name, 
                         JrnlHdr.TransactionDate,
                         JrnlHdr.Reference)
                     FROM Customers
                     INNER JOIN JrnlHdr ON Customers.CustomerRecordNumber = JrnlHdr.CustVendId
                     WHERE JrnlHdr.JrnlKey_Journal = 11 
                       AND JrnlHdr.TransactionDate = CURDATE()
  </code></pre>
  
  <p>-------------------// i tried as follows-----</p>
  
  <pre><code>--&gt; You only do this one time...not with each query
  create sequence dbo.WorkOrderSequence
    as int
    start with 43236
  
  
  --&gt; I took out the part that failed (you got option 1 and 3 kinda
  --&gt; mashed together)
  insert DB1.dbo.WORKORDERS 
     ([WORK ORDER #],[CUSTOMER],[SO DATE],[SO NUMBER],[ASSY PN-S],[CUSTOMER PN],[SHIP VIA],[PROMISED DATE],[COMMENTS],[PO #],[WO Notes])
  select 
    convert(varchar(10), next value for DB1.dbo.WorkOrderSequence ),
    x.Customer_Bill_Name,
    x.TransactionDate,
    x.Reference,
    x.ItemID,
    x.PartNumber,
    x.WhichShipVia,
    x.ShipByDate,
    x.Comment2,
    x.CustomerInvoiceNo,
    x.SalesDescription
  from
    openquery
    ([DB29],
      'select distinct           
         Customers.Customer_Bill_Name,
         JrnlHdr.TransactionDate,
         JrnlHdr.Reference, 
         LineItem.ItemID ,
         LineItem.PartNumber, 
         Customers.WhichShipVia, 
         JrnlHdr.ShipByDate, 
         JrnlHdr.Comment2,
         JrnlHdr.CustomerInvoiceNo, 
         LineItem.SalesDescription    
  FROM   Customers
  INNER JOIN JrnlHdr
  ON Customers.CustomerRecordNumber = JrnlHdr.CustVendId
  LEFT OUTER JOIN Address
  ON Customers.CustomerRecordNumber = Address.CustomerRecordNumber
  INNER JOIN JrnlRow
  ON JrnlHdr.PostOrder = JrnlRow.PostOrder
  INNER JOIN LineItem 
  ON JrnlRow.ItemRecordNumber = LineItem.ItemRecordNumber
  WHERE  JrnlHdr.JrnlKey_Journal = 11 AND JrnlHdr.TransactionDate = CURDATE() 
  AND JrnlHdr.PostOrder = JrnlRow.PostOrder  
  AND JrnlHdr.CustVendId = Customers.CustomerRecordNumber 
  AND JrnlRow.ItemRecordNumber = LineItem.ItemRecordNumber 
  AND JrnlHdr.POSOisClosed = 0' 
  ) as x
  </code></pre>