from numeric id to uniqueidenfier

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

from numeric id to uniqueidenfier

Author

Topic

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2009-02-06 : 06:47:27

we are planning to modify the data type of a field from numeric(auto increment)to uniqueidentifier while maintaining the existing records.
at the same time the all the id i change from a certain tables will affect other table where that id is involve.

sample tables:
tblA
idA nameA
1a A (idA primary key)
2a B
3a C

tblB
idB idA nameB
1b 1a D idA(foreign key from tbl1)
2b 2a E
3b 3a F

tblC
idC idB nameC
1C 1b G idB(foreign key from tbl2)
2C 2b H
3C 3b I

this is what i want to happen to all tables:
tblA
idA nameA
aaaa A (id aaaa is only a sample uniquieidentifier)
aaab B
aaac C

tblB
idB idA nameB
bbba aaaa D
bbbb aaab E
bbbc aaac F

tblC
idC idB nameC
ccca bbba G
cccb bbbb H
cccc bbbc I

i need your expert advice, tips, ideas on how am i going to do this.
let me add that i have more than 20 tables in our database.

thank you in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 09:14:52

you cant alter a identity column easily. i think alternative would be to create a new uniqueidentifier field with default value of NEWID() or NEWSEQUENTIALID(), then based on current value of id in referred tables ,replace id value by unique identifier value. make sure you remove fk constraints on column that relates to id value. after updation, recreate fk constraint to point to new unique identifier column.

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2009-02-08 : 20:53:21

ohh i get it.. thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-08 : 21:13:46

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources