SQL Server query add sequential number to resuls

Question

I have emergency contact information attached to contact records that I am making into their own contact records.

I need to assign a temporary id to the new contact records for migration purposes.

I was hoping to do this within my SQL query that retrieves the data for creating the new records.

I found references to updating the SQL table to add a new column, but that's not what I want. I can do it after in Excel, but would prefer a one step process. Is there a way to do this?

Check these : https://msdn.microsoft.com/en-IN/library/ms186734.aspx and http://stackoverflow.com/questions/15963129/how-to-use-rownum — Rohit Vipin Mathews, May 26 '16 at 16:10

score 2 · Accepted Answer · answered May 26 '16 at 16:26

2

If this is a one time thing you can use row_number()

SELECT 
  *,
  ROW_NUMBER() OVER (ORDER BY SomeColumn) as TempID
FROM YourTable
WHERE...

answered May 26 '16 at 16:26

SQLChao

7,709
1
17
32

This worked perfectly for what I asked... Is there any way to tell it what # to start at? – db Neophyte May 26 '16 at 17:17
@dbNeophyte Yes just do something `ROW_NUMBER() OVER (ORDER BY SomeColumn) + 10` – SQLChao May 26 '16 at 18:48
Fantastic! Just what I needed, and much simpler than what I found. thanks to marc_s for his suggestions too – db Neophyte May 26 '16 at 19:36

SQL Server query add sequential number to resuls

1 Answers1