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

why not change `[WORK ORDER #]` to `BIGINT` and set it to `AUTO INCREMENT` — , Apr 08 '17 at 07:04
@Arun but If i change it to bigint and set autoincrement it will reset all i have missing records so i will lose the history — Joe, Apr 08 '17 at 07:08
How about using row_number? You need to get rid of the distinct, or do a derived table and put the row number outside it. — James Z, Apr 08 '17 at 07:23
Thanks @james but `WORK ORDER #` have its own formatting starts at 500XXX an i have 2000 records — Joe, Apr 08 '17 at 07:32
@Joe `WORK ORDER #` is numeric right? I mean you have said it starts at `500XXX` here `X` represents a digit from `0` to `9` or can it be an alphabet ? — , Apr 08 '17 at 07:44
@ Arun yes it is but numeric value but there are missing or deleted records in between — Joe, Apr 08 '17 at 07:46
@Joe, a number of things: First, you mixed up options 1 and 3. Second, Option 3 apparently isn't supported by the OLEDB provider (or maybe DB29). Also, you didn't provide the complete query in your original post...and so you had to wing it. For future reference, you should definitely clean up your formatting when posting. I fixed the broken part of your attempt. Finally, you only want to run that create sequence statement once...after that, it's part of the database and if you run it again, you'll get errors. — Clay, Apr 08 '17 at 23:00
Thank you so much @Clay, can i implement this using trigger? — Joe, Apr 08 '17 at 23:16
Well...you'd have to implement it in a query on one of the [db29] tables. There's nothing in [db1] to trigger on. But, if you have access to that, then yes, you could do it in a trigger. — Clay, Apr 08 '17 at 23:47
@Clay can you check this question for me http://stackoverflow.com/questions/43333026/does-sql-server-trigger-has-order-of-execution?noredirect=1#comment73730750_43333026 — Joe, Apr 10 '17 at 23:10

Clay · Accepted Answer · 2017-04-08T18:56:24.770

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.

Thank you @clay I like Option 1 and 3 on option 1 i have to create `WorkOrderSequence` table with `sequence` field? — Joe, Apr 08 '17 at 20:43
No - the `WorkOrderSequence` is a `Sequence object`...it's a new thing as of SQL Server 2012. You don't need to make a table at all. Look [here](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql) — Clay, Apr 08 '17 at 21:45
Option 3 doesn't require anything be added to your DB at all. — Clay, Apr 08 '17 at 21:46
Thank you som much! sorry for the delay, i edited my question to show you how i implement option1 — Joe, Apr 08 '17 at 22:36
I'm afraid you made a mess out of it and mixed up option 1 and 3 :-) — Clay, Apr 08 '17 at 22:39
i get THIS error OLE DB provider "PervasiveOLEDB.11.0" for linked server "DB29" returned message "One or more errors occurred during processing of command.". OLE DB provider "PervasiveOLEDB.11.0" for linked server "DB29" returned message "[Pervasive][ODBC Engine Interface]Syntax Error: select distinct row_number() over<< ??? >>( order by JrnlHdr.TransactionDate ) as RowNum, Customers.Customer_Bill_Name, — Joe, Apr 08 '17 at 22:39

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

I've dabbled a little with SQL but not to create tables as much. I have used postgreSQL along with knex to do so and I believe the solution that should fit for your needs as well is using the value unique. I apologize if that's not correct since I am junior to coding, but hopefully looking at this will help or looking at the SQL docs :) difference between primary key and unique key

edited May 23 '17 at 12:32

Community

1
1

answered Apr 08 '17 at 07:14

Sanjeet Uppal

39
1
11

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

2 Answers2