How to auto increment a value when using Insert Into stmt.

[batteam]

I am adding multiple rows to a table using the Insert Into stmt. that gets multiple rows from another table.

My problem is that I need to increment a value (1,2,3,4 etc.) in a field when my Insert Into selects more than one row from the originating table. If I start at 1, for example, how can I get the Insert Into code to fill a column with numbers that increase by one as each new row is added?

Thanks for any help you can provide.

 

[gmmastros]

If you are using SQL2005 or newer, you can use the Row_Number function for this:

[tt][blue]
Insert Into YourTableName(Col1, Col2, IncrementingCol)
Select Col1, Col2, Row_Number() Over(Order By Col1)
From SomeOtherTable
Where SomeCondition = True
[/blue][/tt]

-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

 

[batteam]

Thanks gmmastros. Works great.

 

[batteam]

Actually, it doesn't quite do what I need. What I really need is for that column to increment starting at a given number, not starting at 1 but let's say 6. The value of my increment column after inserting three new records, for example, would be 7, 8 and 9, not 1,2 and 3.

Any ideas? Thanks.

 

[gmmastros]

Row_Number returns an integer, so simple addition should work. Ex:

[tt][blue]
Insert Into YourTableName(Col1, Col2, IncrementingCol)
Select Col1, Col2, [!]6 + [/!]Row_Number() Over(Order By Col1)
From SomeOtherTable
Where SomeCondition = True
[/blue][/tt]

-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

 

[batteam]

Thanks. I did search the internet and SQL help but did not find this easy solution with the syntax you just provided. works like I need it to now.

Thanks.

 

[gmmastros]

You're welcome.

-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