Inserting records with an incrementing value

[kaymc]

I have an INSERT INTO SELECT query (which should insert around 80000 records), where I need to ensure new unique values are assigned to one of my key fields. No, it's not an auto-increment field. No, I don't plan on changing that fact.

If it where an update I could declare a variable and use the set to update it, but I can't find a good way to do something similar with insert into.

 

[sunila7]

Hi,

I think easiest way of doing this is to add a field which is set as IDENTITY in the your table and then Insert all the rows u want into the table and then u have an autonumbered field in ur table...

Then u can set the IDENTITY property of the field to false. Generating a sequence no for 80000 rows will be very slow...



Sunil

 

[miq]

hi,

If you don't like identity field that prepare to much overloaded query

This can be achieved through the use of cursor. Traverse rows one by one generate an incrementing number using variable ike @incrm = @incrm + 1. use that variable as a field value. Note: Things will drastically slow down with cursors so, schedule action query execution for off peak hours.


Second solution: use temporary table with single field having 800000 records containg 1 in first row, 2 in second row and so on. Provide that table as a source for field value in your action query.


bye
miq