How to get auto-increment column in SELECT query

• SAMDEV

  SSC-Addicted

  Points: 494

  More actions

  January 13, 2015 at 4:39 am

  #314548

  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

• Grant Fritchey

  SSC Guru

  Points: 399058

  More actions

  January 13, 2015 at 4:50 am

  #1769755

  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

• Brandie Tarvin

  SSC Guru

  Points: 173105

  More actions

  January 13, 2015 at 6:32 am

  #1769781

  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.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.