How can I insert autoincrement value using SQL insert into select statement

Question

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 [WORK ORDER #] is a primary key it doesn't allow null.

[WORK ORDER #] is nvarchar(10)

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()

-------------------// i tried as follows-----

--> You only do this one time...not with each query
create sequence dbo.WorkOrderSequence
  as int
  start with 43236


--> I took out the part that failed (you got option 1 and 3 kinda
--> 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

Clay · Accepted Answer

Option 1

If you're on at least SQL Server 2012 (you didn't mention a specific version), you have a general sequence number generator that you can use. I like it a lot for this kind of scenario. In the DB1 database, you'd add your sequence like this:

create sequence dbo.WorkOrderSequence
  as int
  start with 5002230 --> pick a starting number greater 
                     --> than any existing [WorkOrder #]

Then, you can just get the next number(s) in your insert-for-select statement:

insert DB1.dbo.WORKORDERS 
  ([WORK ORDER #], [CUSTOMER], [SO DATE], [SO NUMBER])
select 
  convert(varchar(10), next value for DB1.dbo.WorkOrderSequence ),
  x.Customer_Bill_Name,
  x.TransactionDate,
  x.Reference
from
  openquery
  ([DB29],
    'select distinct 
       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()' 
  ) as x

The sequence is a standalone auto-incrementing number. Every time you use the next value for dbo.WorkOrderSequence, it auto-increments. This way, you don't have to modify any table definitions.

Option 2

Alternatively, you could alter the DB1.dbo.WORKORDERS table so that the default value to use the expression...

alter table dbo.WORKORDERS
  alter column [Work Order #] nvarchar(10) not null
    default( convert( nvarchar(10), next value for dbo.WorkOrderSequence ) )

If you do this, then you can completely omit inserting the [Work Order #] altogether and let the default do the magic.

Option 3

If you're not on 2012, but on at least 2008, you can still get there...but it's a little trickier because you have to get the current starting [Work Order #]:

insert DB1.dbo.WORKORDERS 
  ([WORK ORDER #], [CUSTOMER], [SO DATE], [SO NUMBER])
select 
  convert(varchar(10), x.RowNum + y.MaxOrderNum ),
  x.Customer_Bill_Name,
  x.TransactionDate,
  x.Reference
from
  openquery
  ([DB29],
    'select distinct 
       row_number() over( order by JrnlHdr.TransactionDate ) as RowNum,
       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()' 
  ) as x
  cross join
  (
    select 
      convert( int, max( [Work Order #] ) ) as MaxOrderNum
    from 
      Db1.dbo.WORKORDERS
  ) as y

Option 4

If you're on something earlier than 2008...you'll probably want a stored procedure to do the insert in two steps: inserting the work orders into a temporary table (one with an auto-increment [Work Order #] starting at the current table's max( [Work Order #] ) + 1 )...and then step 2 would insert the temp table into WORKORDERS with a convert.

How can I insert autoincrement value using SQL insert into select statement

Answers (2)

Related Questions