Software

Ask something
Discussion Locked

Create a unique id for each row in MSS

0 helpful
1 reply

by ddesilva

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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)

THNNKS

1 reply

by glen_mcleod

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),
OrigTableCol_1,
OrigTableCol_2,
...
)

//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)

Glen

This discussion is closed to new replies.