General discussion


Create a unique id for each row in MSS

By ddesilva ·
I need to create few txt files from an existing MSS db using some scripts. In the txt file, I need to create a new id column for each row. I like to use some function in my SQL scripts so it will create a unique id for each row. Any ideas?

Id column needs to be less that 18 characters.(I cannot use NEWID() function)


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Table variable

by Glen_McLeod In reply to Create a unique id for e ...

Use a stored procedure as the source of the data to export, declare a table variable as having an Identity column as the first column, then define the rest of the columns from your original table.

Declare @TextSource table(
NewCounter int Identity (1,1),

//fill the columns with the data from the original table, NewCounter gets incremented automatically
Insert into @TextSource (OrigTableCol_1, OrigTableCol_2, ...)
Select OrigTableCol_1, OrigTableCol_2, ...
From OriginalTable
Where (if necessary) ...

//return the contents of the table variable
Select * from @TextSource

This will give you your original table plus an identity column. You can start the counter at any value you want and increment it by any value you want as well, just change the seed and increment values when you declare it.

NewCounter int Identity(seed, increment)


Related Discussions

Related Forums