Converting numeric to unique identifier.

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)

Converting numeric to unique identifier.

Author

Topic

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-09-10 : 04:49:29

I am working on migrating data from existing health application database(SQL Server 2005) to another database(SQL Server 2005). The structure of both the database is quite different.

In existing database, we are Using bigint for primary key purpose. In the new database we are using UniqueIdentifier datatype for primary Key.

Is there any way to convert the integer values to UniqueIdentifier.

Sample Data:

CREATE TABLE utest
(a nvarchar(100))
GO

INSERT INTO utest values('85234974')
GO
INSERT INTO utest values('74524974')
GO
INSERT INTO utest values('23698974')
GO

CREATE TABLE T2 ( b uniqueidentifier)
GO
INSERT INTO T2 SELECT convert(uniqueidentifier,a) FROM utest
GO

Error Received:

Server: Msg 8169, Level 16, State 2, Line 1
Syntax error converting from a character string to uniqueidentifier.

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 06:04:13

[code]DECLARE @Sample TABLE (a nvarchar(100))

INSERT @Sample
SELECT '85234974' UNION ALL
SELECT '74524974' UNION ALL
SELECT '23698974'

SELECT a AS Original,
CAST(STUFF(STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 32) + a, 32), 21, 0, '-'), 17, 0, '-'), 13, 0, '-'), 9, 0, '-') AS UNIQUEIDENTIFIER) AS UID
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-09-10 : 06:35:03

Thanks a lot Peso for quick reply and for the solution i was looking for.
Thanks once again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources