Convert From Varchar to Uniqueidentifier in Update Table Statement

[Ambsgirl]

I need to alter a varchar column to be a uniqueidentifier in code so I've created a temporary column that is a uniqueidentifier and need to copy the values from the old column to my temporary column. Only this generates an error that it cannot convert a varchar into a uniqueidentifier. Enterprise Manager creates a temporary table and uses this statement: IF EXISTS(SELECT * FROM dbo.test)
EXEC('INSERT INTO dbo.Tmp_test (t_NAME, t_TYPE, t_NEW_FLAG, RowGuid)
SELECT t_name, t_TYPE, t_NEW_FLAG, CONVERT(uniqueidentifier, RowGuid) FROM dbo.test TABLOCKX').

Can this statement be modified into an update statement? Everything I've tried so far gives me that same error that it cannot convert a varchar into a uniqueidentifier.

 

[SQLSister]

look up uniqueidentifer datatype in BOL.

Questions about posting. See faq183-874

 

[Ambsgirl]

I get the concept of a uniqueidentifier. I have a program that our customers use to upgrade their databases. It generates scripts of our "master" database which we send to our customer. The program compares those scripts to their database and makes the necessary changes. For columns that need to be altered such as this one, it creates a temporary column, copies the data from the old one, deletes the old one and renames the new much as Enterprise Manager does except Enterprise Manager creates a temporary table rather than a temporary column. The problem is this one needs to be converted before it can copied. The existing data in the varchar column is formatted as a uniqueidentifier. The difference is our software used to generate it itself and insert it whereas now it's been changed to allow SQL Server generate it as it should. Can I convert this data which is saved in as a varchar but formatted as a uniqueidentifier into a uniqueidentifier column?

 

[gmmastros]

Can you show us a couple records of what the data looks like right now?

Select Top 10 IdField From Table

Copy paste that data here. I suspect that it doesn't really look like a unique identifier. For example, there might be curly brackets around the data or you might be missing the dashes. Nonetheless, I create a test table with a varchar column. I then added a dozen records to it. Then, I added another column (of type UniqueIdentifier) and update the table successfully. Here's what I did.

[COLOR=blue]If[/color] Exists([COLOR=blue]Select[/color] * 
          [COLOR=blue]from[/color]   Information_Schema.Tables 
          [COLOR=blue]Where[/color]  Table_Name = [COLOR=red]'Test'[/color] 
                 and Table_Type = [COLOR=red]'Base Table'[/color])
	[COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] Test

[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] Test(id [COLOR=blue]varchar[/color](100))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] Test(id) [COLOR=blue]Select[/color] NewId()
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] Test(id) [COLOR=blue]Select[/color] NewId()
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] Test(id) [COLOR=blue]Select[/color] NewId()
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] Test(id) [COLOR=blue]Select[/color] NewId()
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] Test(id) [COLOR=blue]Select[/color] NewId()
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] Test(id) [COLOR=blue]Select[/color] NewId()
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] Test(id) [COLOR=blue]Select[/color] NewId()

[COLOR=blue]Select[/color] * [COLOR=blue]from[/color] Test

[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] Test [COLOR=blue]Add[/color] id_Temp UniqueIdentifier 

[COLOR=blue]Update[/color] Test [COLOR=blue]Set[/color] id_Temp = id

[COLOR=blue]Select[/color] * [COLOR=blue]from[/color] Test

[COLOR=blue]Alter[/color] [COLOR=blue]table[/color] test [COLOR=blue]drop[/color] [COLOR=blue]column[/color] id

[COLOR=blue]exec[/color] sp_rename [COLOR=red]'Test.id_Temp'[/color], [COLOR=red]'id'[/color], [COLOR=red]'column'[/color]

[COLOR=blue]Select[/color] * [COLOR=blue]from[/color] Test

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ambsgirl]

Here are the the top 5 values I'm getting from my table from the old rowguid:
{DDA3091E-9833-40F5-AFB8-57C540C85FA}
{2DB57701-D310-4B21-A9AA-5F7AFD99D85}
{0D982009-0E4A-4B7C-99C2-B7E45B876E9}
{8A5B9B01-B866-4DCF-AE93-00BAC0BA4CD}
{EF84594A-E22F-43B6-9E51-0EF24F09B58}

But you're definitely right because if I reset my rowguid values to newid(), I can then copy them to the new table without a convert or cast. And that tells me what I need to know. Guess I'll have to let them be recreated with a default newid() as long as there is nothing expecting those values to stay the same.

Thanks a million for your help, Gmmastros. I always appreciate people who are willing to roll up their sleeves and truly help out.

 

[gmmastros]

Hmmmm.....

I'll admit that I don't know all there is to know about GUID's, NewId() and unique identifiers.

It does appear, however, that your guids are 1 character shy. As it turns out, you can keep curly brackets and it will convert fine. You can't, however, has less characters than you need.

Ex:

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Convert[/color](UniqueIdentifier, [COLOR=red]'{DDA3091E-9833-40F5-AFB8-57C540C85FA}'[/color])
[COLOR=blue]Select[/color] [COLOR=#FF00FF]Convert[/color](UniqueIdentifier, [COLOR=red]'{DDA3091E-9833-40F5-AFB8-57C540C85FAA}'[/color])

The first convert will fail. For the second one, I added another character to the end of the GUID and it converts correctly. I don't know why or how. This is merely an observation.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom