January 13, 2015 at 4:39 am
I know IDENTITY function can be used for this but then it needs SELECT...INTO which inserts into new table only.
I want to insert into an existing table but while inserting i am getting two columns from a table. Along with these columns, I want one more column to be inserted which will be having auto-incrementing values and whose starting value I will set.
I want something like
SELECT NewCol, FieldLabel, FieldValue FROM Table 1
In this, Newcol will be auto incrementing. If I set its value to 6, it will have values like 7,8,9..... for each record my SELECT query returns.
I hope I am clear in putting my requirement
January 13, 2015 at 4:50 am
You could use the ROW_NUMBER function and just add your starting value to output of the query. For example, if you want to start counting at 7, you add 6 to the output of ROW_NUMBER and you'll see 7, 8, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2015 at 6:32 am
You also need to be careful if you are inserting into a column that already has the identity property set. Check out the SET IDENTITY_INSERT statement in Books Online.
But if the table doesn't have the identity property set on the column in question, don't worry about this information.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply