Create a Unique ID for Rows

Question

Create a Unique ID for Rows

neelubawa33

SSC Enthusiast

Points: 116
More actions
February 1, 2012 at 12:26 pm

#252147

I have a table. It desn't have any Id which will be generated automatically.
my table (Table1)has 30 some rows and 3 columns(FirstName, LastName, Occupation).
I want to read each row one by one and wanted to create a unique ID which is of type uniqueIdentifier and fill another table with all these coulmns and newly created ID.
How can I do that

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Look at the output clause. http://msdn.microsoft.com/en-us/library/ms177564.aspx

For the record unless you have a real reason for uniqueidentifier I would not use them as your primary key. I would suggest that an (big)int is a better choice. There are a number of reasons not to use uniqueidentifiers keys but that is really outside the scope of your question.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Recurs1on SSCommitted Points: 1974 More actions · Answer 2

I agree about the uniqueidentifier probably not being the best choice, but leaving that alone, here's something that may work for you.

This doesn't read things row by row but I'm trying to interpret your requirements here. If you must read row by row, please let us know. This will do it as a set.

SELECT

NEWID() AS TableID,

*

INTO NewTable

FROM OldTable

or if you want to use an INT you can use something like

SELECT

ROW_NUMBER() OVER( ORDER BY ColumnName ) AS TableID,

*

INTO NewTable

FROM OldTable

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

There is absolutely no reason to do this row by row. Below is an example that meets the requirements as you described them.

create table #Table1

(

FirstName varchar(20),

LastName varchar(20),

Occupation varchar(70),

ID uniqueidentifier

)

create table #Table2

(

FirstName varchar(20),

LastName varchar(20),

Occupation varchar(70),

ID uniqueidentifier

)

insert into #Table1 (FirstName, LastName, Occupation)

select 'Andrew', 'Price', 'Admissions director'

union all

select 'Helen', 'Love', 'Contract manager'

union all

select 'Cecilia', 'Riley', 'Personnel technician'

union all

select 'Kathryn', 'Servantes', 'Masseuse'

union all

select 'Marsha', 'Rodriguez', 'Credit authorizer'

union all

select 'Donald', 'Colyer', 'Electric motor repairer'

union all

select 'Jan', 'Bravo', 'Power tool repairer'

union all

select 'Dawn', 'Scott', 'Gas plant operator'

union all

select 'Peter', 'Jones', 'Personnel assistant'

union all

select 'Pauletta', 'Holland', 'Airport service agent'

select * from #Table1

update #Table1 set ID = NEWID()

output inserted.* into #Table2

select * from #Table1

select * from #Table2

drop table #Table1

drop table #Table2

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 4

Hi, guys.

Adding an IDENTITY column wouldn't solve his problem?

ALTER TABLE dbo.TableObject ADD Id INT IDENTITY UNIQUE;

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Revenant SSC-Forever Points: 42467 More actions · Answer 5

No, that will not do -- if you read the original post, it calls for a UNIQUEIDENTIFIER, not for an INT.

You have to cast is as a UNIQUEIDENTIFIER with default value NEWID().

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 6

Sorry then, I thought that he was after an "unique ID" and that a UNIQUEIDENTIFIER would be an option but as almost everyone here said NO to using a UNIQUEIDEINTIFIER (unless needed) I was advising him to use an IDENTITY column. That would be an option (if he was not to use an UNIQUEIDENTIFIER column, that is). 😉

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA