Search code examples
sqlsql-serveruniqueidentifiersqldatatypesnvarchar

How do I convert a nvarchar to a uniqueidentifer?

I have a table (RPT.table1) that contains data that was exported from an ArcGIS Online application. One of the columns in the table (GlobalID) was exported as a nvarchar(255) datatype. I need to convert this column to a uniqueidentifier datatype before I insert this data into another table (CFAdmin.table2).

The values in the GlobalID column were once unique identifiers and already contain hyphens (B4A6AA96-42DF-48D9-A3E0-4C7F88ED3E1D).

I've tried using 

ALTER TABLE RPT.table1 
ALTER COLUMN GlobalID uniqueidentifier;

and in an INSERT INTO statement

INSERT INTO CFAdmin.table2 (GlobalID)
    SELECT CAST(GlobalID AS uniqueidentifier)
    FROM RPT.table1;

For both methods I get an error

Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to uniqueidentifier.

I'm relatively new to SQL Server and am guessing there is a pretty simple solution.

Thanks for reading.

Solution

  • Chances are there's an issue with your data, not with your code/syntax. There are probably values in the source table that are invalid as unique identifiers. I would investigate by just looking at the data in your source table and trying to find the values that would cause the error. See what this query returns:

    Select GlobalID from RPT.table1 WHERE GlobalID NOT LIKE '________-____-____-____-____________'

    (Those underscores blur together, so for the sake of being explicit: 8 underscores, dash, 4 underscores, dash, 4 underscores, dash, 4 underscores, dash, 12 underscores)

    And maybe check 

    Select GlobalID from RPT.table1 WHERE GlobalID IS NULL