Auto increment value in insert statement

Question

HI, I have a problem with a query trying to generate an auto increment value in an insert statement:

for example:

insert into table1
 select col1,	    
	    (select coalesce(max(col2), 0) + 1 from table1 WITH (HOLDLOCK, UPDLOCK)),
	    col3,
	    col4,
    from table2

its supposed to auto increment the value of col2 for each insert and besides avoid getting the same value 
from people accessing at the same time. I can't use and identity column, because requires lot of changes.

I'm sure I did it before and was working, but now I don't why is not changing the value...

Thank you.    

Answer 1

Try

insert into table1
 select col1,	    
	    (select coalesce(max(isnull(col2,0)), 0) + 1 from table1 WITH (HOLDLOCK, UPDLOCK)),
	    col3,
	    col4,
    from table2

---

Many Thanks & Best Regards, Hua Min

Answer 2

Does table2 have more than 1 row? Are you trying to insert into a primary key column?

If so, this will not work. You need to get the max number first into a variable and then use ROW_NUMBER() function to add to that max number so each row of table2 will get its own number.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

If Table1 has no record the SELECT statements from Table1 will return nothing

(select coalesce(max(isnull(col2,0)), 0) + 1 from table1 WITH (HOLDLOCK, UPDLOCK)),

These statements will fail if Table1 is empty

I strongly suggest to face with a lot of work and get rid of this identity problem

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 4

I combined Row_Number() suggestion of Naomi to overcome if Table1 is empty (so for first insert statement)

insert into table1
 select col1,	    
		isnull(
		(	select max(isnull(col2,0))+1 from table1 WITH (HOLDLOCK, UPDLOCK)	)
		, 
		ROW_NUMBER() over (order by col2) 
		),
	    col3,
	    col4
    from table2

select * from table1
select * from table2

This works for first INSERT on table1

But for following insers statements, if table2 has more than 1 row, each time a new INSERT statement is executed all rows from TABLE2 will have same IDs on Table1

This is not a good solution, still insist on using auto-increment IDENTITY column in your table

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 5

Hi rguarnieri.

May This code will Helpful. Your insert method is wrong in sql .

declare @c2 int -- or any type (numerials)
set @c2 =  select coalesce(max(col2), 0) from Tabel1
set @c2 = @c2+1
insert into table1(col1,col2)  --  your names maybe are others
 select col1, @c2 from table1 WITH (HOLDLOCK, UPDLOCK)
insert into table1(col3,col4)  --  your names maybe are others
select col3,col4 from table2

Answer 6

Hi,

Please try the following code,

Insert into table1 (col1, col2, col3, col4)
select col1, 
	isnull(( select max(isnull(col2,0))  from table1 ),0) + ( ROW_NUMBER() over (order by col2) ),
	col3, col4 
from table2

It is tested :)

I hope it helps,

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 7

Elarper,

I meant to add ROW_NUMBER, e.g.

insert into table1
 select col1,	    
		isnull(
		(	select max(isnull(col2,0))+1 from table1 WITH (HOLDLOCK, UPDLOCK)	),0) + 
		
		ROW_NUMBER() over (order by col3) 
		) as Col2,
	    col3,
	    col4
    from table2

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

Hi Naomi,

After some tries, I come to your suggesstion. At first I missed to understand it but later the last script is using MAX() statement with Row_Number() as you suggested.

Thanks..

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 9

Thank you very much eralper and Naomi!

Is working!