generate sequence in sql select

Question

I need to write a query that will generate a sort of sequenced ID for each record... so for example:

ID      Customer Name
-------------------------
C1000   customer #1
C1010   customer #2
C1020   customer #3
C1030   customer #4

now, these "C1000" ids don't exist... only the customer names. I need to generate them when I do the select... so I can save off the output, and then import into the new system.

how can I do a:


select 
   'C' + (some kinda code/math that generates the count based on a sequence?  or row number?),
   name
from Customers

================================================

I ended up doing the following (so I could configure start# and increment size):


DECLARE @start int; 
DECLARE @inc int; 
set @start = 1000; 
set @inc = 10; 

Select 'C' + CAST(@start + (@inc * (ROW_NUMBER() OVER (ORDER BY name))) as varchar) as NewID, Name
from customer

You'll need something to order by or the generated sequences will be unpredictable (will change between runs). — Remus Rusanu
– Remus Rusanu, Commented Jul 22, 2009 at 16:53

JP Alioto · Accepted Answer · 2009-07-22 15:54:55Z

2

Use ROW_NUMBER() as in this example.

answered Jul 22, 2009 at 15:54

JP Alioto

45.2k6 gold badges91 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

generate sequence in sql select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related