none
Insert nvarchar into uniqueidentifier column

    Question

  • I have a complex situation where I generate data from a pivot table and need to insert the results into a #temp table.  One of the columns in the #temp table is a uniqueidentifier data type, however, since the data is dynamically generated from a pivot table, the pivot table's output is nvarchar.  my solution was to first declare the temp table column's data type as narchar(36) and insert the results into it like this:

    CREATE TABLE #tmp1 (RowId uniqueidentifier, RowHeader nvarchar(15), SomeColumn nvarchar(36) )

    INSERT INTO TABLE #tmp1 (RowId, RowHeader, SomeColumn)

    EXEC dbo.MyCrosstabSproc

    -- so far, so good

    select * from #tmp1

    -- sample results:

    --568BC431-1252-4C2D-86C1-C4656E1D717E someValue      568BC431-1252-4C2D-86C1-C4656E1D717E

    -- Now the part I'm having trouble with converting.

    -- I need to insert the nvarchar value into a uniqueidentifier column like this:

    CREATE TABLE #tmp2 (RowId uniqueidentifier, RowHeader nvarchar(15), SomeColumn uniqueidentifier )

    INSERT INTO TABLE #tmp2 (RowId, RowHeader, SomeColumn)

    SELECT RowId, RowHeader, CAST(SomeColumnas uniqueidentifier)

    I will get the error:

    Conversion failed when converting from a character string to uniqueidentifier.

    Thanks for any help you can offer!

    Saturday, September 14, 2013 7:01 PM

Answers

  • I found the problem.  it turns out that some rows, the value of SomeColumn was '' rather than null.  I assumed it would be null.

    So I had to change my select statement from:

    SELECT RowId, RowHeader, CAST(SomeColumnas uniqueidentifier)

    to

    SELECT

    RowId,

    RowHeader,

    CASE
        WHEN SomeColumnas = '' THEN
        NULL
        ELSE
        Ls_LsAltNbrSrc_Id
        END AS Ls_LsAltNbrSrc_Id

    and everything worked.  SQL Server did the casting for me.

    • Marked as answer by moondaddy Saturday, September 14, 2013 7:48 PM
    Saturday, September 14, 2013 7:48 PM

All replies

  • I found the problem.  it turns out that some rows, the value of SomeColumn was '' rather than null.  I assumed it would be null.

    So I had to change my select statement from:

    SELECT RowId, RowHeader, CAST(SomeColumnas uniqueidentifier)

    to

    SELECT

    RowId,

    RowHeader,

    CASE
        WHEN SomeColumnas = '' THEN
        NULL
        ELSE
        Ls_LsAltNbrSrc_Id
        END AS Ls_LsAltNbrSrc_Id

    and everything worked.  SQL Server did the casting for me.

    • Marked as answer by moondaddy Saturday, September 14, 2013 7:48 PM
    Saturday, September 14, 2013 7:48 PM
  • Hi moondaddy,

    Thanks for your post and sharing. Your post will help others encounter the same issue. Thanks for your effort again.


    Allen Li
    TechNet Community Support

    Monday, September 16, 2013 2:12 AM