Insert rows into primary key column - How to increment by one

[bmacbmac]

I have a table 'Mytable' with the following columns

recordid int (Primary Key)
NameID int
Name varchar (75)

I have data in a #temp table that I would like to insert into mytable.

The #temp table has columns:
NameID int
Name Varchar (75)

I would like to insert this data into MyTable and allow the recordid to increment (like an identity column).

I tried running:

insert into mytable
select (select max(recordid) + 1), NameID, Name)
from #temp

But I get an error that I cannot insert duplicate key in object 'mytable'. I discovered this is because it wants to make the recordid the same number for all rows (max(recordid) + 1)

Is there a way I could insert one row at a time and each time set the recordid to 1 + max(recordid)?

Or can you think of another way I should do this?

I could just create the new recordid list in another #temp table, but this is a scheduled script I would like to run each night and hoping I can automate an insert statement.

This is SQL 2000.

Thanks!

Brian

 

[gmmastros]

Add an identity column to your temp table.
Get the Max + 1 from real table in to @Variable

Insert Into mytable(recordid, col1,col2)
Select TempTableIdentityCol + @Variable, Col1, Col2
From #TempTable

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Genius! Thanks!