CODEWITHSUNDEEP
sql-serversql-server-2008
RKh
RKh

Reputation: 14161

Get Incremented IDs when Identity is not set

I have a table where primary key column has IDENTITY OFF. While performing an INSERT, I pick the max(Cust_Id)+1 and inserts.

But sometimes there are multiple records to be inserted using a query similar to this:

INSERT INTO <table_1>
SELECT 
   (SELECT MAX(Cust_Id)+1 FROM <table_1>),
   CustName
FROM <TABLE2> 
WHERE Dept = 'Sales' AND Designation = 'Manager'

The SELECT statement often picks multiple records, but all records have max ID column say, 16.

Is there any way to automatically increment the max ID column based on row?

For example:

INSTEAD OF:

16 ........... John

16 ........... Sam

16 ........... Adam

it should pick in this way:

16 ........... John

17 ........... Sam

18 ........... Adam

Upvotes: 0

Views: 42

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

This is a kludge, but it may help you solve the immediate problem. In the long run, though, it's best to turn IDENTITY back on.

DECLARE @Seed INT
SELECT @Seed = MAX(Cust_ID) FROM <table_1>


INSERT INTO <table_1>
SELECT 
   ROW_NUMBER() OVER (ORDER BY Dept) + @Seed,
   CustName
FROM <TABLE2> 
WHERE Dept = 'Sales' AND Designation = 'Manager'

However, this code is likely to break in spectacular ways if you execute multiple instances of it simultaneously.

Upvotes: 1

Related Questions