T-SQL, Insert into with MAX()+1 in subquery doesn't increment, alternatives?

Question

I have a query where I need to "batch" insert rows into a table with a primary key without identity.

--TableA
--PK int (Primary key, no-identity)
--CustNo int
INSERT INTO TableA (PK,CustNo)
  SELECT (SELECT MAX(PK)+1 AS PK FROM TableA), CustNo
  FROM Customers

(simplified example - please don't comment about possible concurrency issues :-))

The problem is that it doesn't increment the PK "for each" processed row, and I get a primary key violation.

I know how to do it with a cursor/while loop, but I would like to avoid that, and solve it in a set-based kind of manner, if that's even possible ?

(running SQL Server 2008 Standard)

+1 @Cybernate - this is a terrible idea and if you had bothered to search on SO there are about 20 questions similar to this, each telling you why it's a bad idea. — JNK, Aug 08 '11 at 15:50
Long story short: i would make an identity if i could :-) . @JNK i bothered allright, but it's my only option, and i want to solve it the best way possible. Also bear in mind i provided a simplified example. — KorsG, Aug 08 '11 at 16:02
Please elaborate on what is stopping you from making it an identity. Yes there are many cases where there is a (better) alternative to identity but if there were no valid uses then identity would not be a feature in the first place. — paparazzo, Aug 08 '11 at 18:09

score 29 · Accepted Answer · answered Aug 08 '11 at 15:50

29

Declare @i int;

Select @i = max(pk) + 1 from tablea;

INSERT INTO TableA (PK, custno)
Select row_number() over(order by custno) + @i  , CustNo
FROM Customers

answered Aug 08 '11 at 15:50

Michael Buen

38,643
9
94
118

score 10 · Answer 2 · edited Sep 25 '14 at 18:42

10

+1 to Michael Buen, but I have one suggestion:

The table "tablea" can be empty, so we should write:

Select @i = isnull(max(pk),0) + 1 from tablea;

This will prevent a null error when trying to use this code.

edited Sep 25 '14 at 18:42

esre

87
9

answered Aug 08 '11 at 16:34

Max

804
7
19

score 4 · Answer 3 · answered Aug 08 '11 at 15:46

4

The problem as you have seen is that they all get the same row number, the max(PK) +1 is the same for every row.

Try convert it to be Max(PK) + Row_number()

I'm working on the basis as to why you know this is a bad idea etc, and your question is simplified for the purpose of getting an answer, and not how you would wish to solve the problem.

answered Aug 08 '11 at 15:46

Andrew

26,629
5
63
86

This will help. http://stackoverflow.com/questions/13845791/insert-multiple-rows-with-incremental-primary-key-sql – Asanka Indrajith Dec 31 '15 at 06:55

score 3 · Answer 4 · answered Aug 08 '11 at 15:51

3

You can;

;with T(NPK, CustNo) as (
  select row_number() over (order by CustNo), CustNo from Customers
)
insert into TableA (PK, CustNo)
  select NPK, custno from T
order by CustNo

answered Aug 08 '11 at 15:51

Alex K.

171,639
30
264
288

score 1 · Answer 5 · answered Jun 18 '15 at 14:12

1

I have a suggestion for you buddy, a better practice on SQL says to use SEQUENCE, and guess what, it´s VERY easy to do it man, just copy and paste mine:

CREATE SEQUENCE SEQ_TABLEA AS INTEGER START WITH 1 INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 NO CYCLE

and use like this:

INSERT INTO TableA (PK,CustNo) VALUES (SEQ_TABLEA.NEXTVAL,123)

Hope this tip able to help ya!

answered Jun 18 '15 at 14:12

Felipe Campanhol

79
4

3

Thanks for answering, but the original question was regarding SQL Server 2008, and SEQUENCE is only available for SQL Server 2012+ – KorsG Jun 18 '15 at 15:46

T-SQL, Insert into with MAX()+1 in subquery doesn't increment, alternatives?

5 Answers5