SQL Auto Increment a value with multiple inserts

Question

We have a SQL query being executed against a SQL Server 2008 DB.

INSERT INTO ListDetail(ListID, Sequence, CompName, CompAddress, Date)
(
 SELECT
   12345 AS 'ListID',
   (MAX(ListDetail.Sequence) + 1) AUTO_INCREMENT as 'Sequence',
   Company.Name AS 'CompName',
   Company.Address AS 'CompAddress',
   GETDATE() AS 'Date'
 FROM Company
 WHERE CompanyType = 3
)

We want to find the max Sequence from ListDetail table.. and select records from the Company table into ListDetail. But, we want to start with the next available Sequence value from ListDetail and then increment by 1 for each record inserted. The Sequence field in ListDetail is just a general INT field.

We do not have control over the database its-self... so created a new table or altering the existing one is not an option.

sgeddes · Accepted Answer · 2014-04-01 21:47:13Z

2

One option would be to use Row_Number() with a subquery that returns the max():

Simplified solution:

insert into ListDetail 
select 12345, sq+row_number() over (order by (select null))
from company, (select max(sequence) sq from listdetail) t

SQL Fiddle Demo

INSERT INTO ListDetail(ListID, Sequence, CompName, CompAddress, Date)
SELECT
   12345,
   sq+row_number() over (order by (select null)),
   Name,
   Address,
   GETDATE()
FROM Company, (select max(sequence) sq from listdetail) t
WHERE CompanyType = 3

answered Apr 1, 2014 at 21:47

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

smoore4 · Accepted Answer · 2014-04-01 21:53:33Z

0

A simple approach like this should work for you.

INSERT INTO ListDetail(ListID, Sequence, CompName, CompAddress, Date)
(
 SELECT
 12345,
 (SELECT MAX(Sequence) + 1 FROM ListDetail),
 Company.Name,
 Company.Address,
 GETDATE()
 FROM Company
 WHERE CompanyType = 3
)

answered Apr 1, 2014 at 21:53

smoore4

4,9263 gold badges43 silver badges64 bronze badges

2 Comments

sgeddes Over a year ago

I don't think this will produce the correct results (although I can see how it should make sense). Check out this fiddle: sqlfiddle.com/#!3/d8ed9/1

adam Over a year ago

Agreed, it does seem to make sense, but I actually tried this at one point and it didn't work.

Collectives™ on Stack Overflow

SQL Auto Increment a value with multiple inserts

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related