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)

Beecher

Why don't u make the column an identity column? – Lookeron

+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. – Alphabetize

Long story short: i would make an identity if i could :-) . @Alphabetize 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. – Beecher

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. – Amphiboly

Answer 1

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

Ecclesiastic

Answer 2

+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.

Thousand

Answer 3

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.

Terrorist

This will help. #13846291 – Brimmer

Answer 4

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 

Coretta

Answer 5

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!

Monophony

Thanks for answering, but the original question was regarding SQL Server 2008, and SEQUENCE is only available for SQL Server 2012+ – Beecher