Answered by:
Insert nvarchar into uniqueidentifier column

-
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!
Question
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_Idand everything worked. SQL Server did the casting for me.
- Marked as answer by moondaddy 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_Idand everything worked. SQL Server did the casting for me.
- Marked as answer by moondaddy Saturday, September 14, 2013 7:48 PM
-