Query to increment by 1 1

[wfairban]

I need to increment by 1 for each record added. There are over 60,000 records I need to add. Any suggestions?

If I do this
INSERT INTO PRODUCTSBYCATEGORY(productid, categoryid, sequence)
(Select productid, 8, (select (max(sequence) + 1)
from productsbycategory
where categoryid = 8)
from product
where (partnumber not like 'ITE%') and
(partnumber not like 'NPO%') and
(partnumber not like 'ENG%')

It will set the sequence to 2, but then it set all records to 2, instead of 3, 4, 5, etc.

 

[jbenson001]

How about using an Identity column?

 

[wfairban]

Could you elaborate? I would normally do this in Oracle with a cursor.

 

[jbenson001]

If you create an Identity column on the table, then each time you insert a row, the Identity column will increment by the seed you specify, the default is one. When you create your insert statment, just don't specify that column.
Example:
Once you create an Identity column, lest call is sequence as you have called it.. then change your Insert to this:


INSERT INTO PRODUCTSBYCATEGORY(productid, categoryid)

The sequence column will increment automatically.

 

[tcardoso]

And when I already have one identity in my table! SQL Server doesn't accepts two identitys in same table...