• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 867
  • Last Modified:

Inserting NVarchar into Guid

I'm trying to insert data from one table to another.

The source table contains an nvarchar field right now that has guids as text.

I want to insert it into a new table that has a UniqueIdentifier field.

This is the query and the error.

insert into Tag
(
    BusinessId,
    AdImage,
    AdSize,
    DayCycleId,
    CreatedOn,
    IsActive,
    Name,
    CreatedBy
)
select cast(ID as uniqueidentifier),
    [IMAGE],
    '1',
    cast('71479D27-D7A2-4439-99CB-0B536DCEFB45' as uniqueidentifier),
    GETDATE(),
    1,
    'NA',
    cast('49493D4F-A0B0-43A5-8B32-45572E4D7E43' as uniqueidentifier)
from IMPORT
Error:

Msg 8169, Level 16, State 2, Line 1

Conversion failed when converting from a character string to uniqueidentifier.
0
Rob Gaudet
Asked:
Rob Gaudet
  • 15
  • 8
  • 7
3 Solutions
 
DcpKingCommented:
DECLARE @ID nvarchar(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0';
SELECT @ID, CONVERT(uniqueidentifier, @ID) AS 'New GUID Value';

Open in new window


hth

Mike
0
 
Rob GaudetAuthor Commented:
Thanks.. As a follow up question.

How can I loop through a recordset putting this solution into place for each row from the source to the final table?

Notice this line in the Insert portion, it needs to be converted to a uniqueidentifier for each row inserted.

select cast(ID as uniqueidentifier)
0
 
DcpKingCommented:
declare @strID nvarchar(max)
-- ---------------
update <tablename>
       set GuidField = null
-- ---------------
while 'xxx' = 'xxx'
begin
   set @strID = (select top 1 ID from <tablename> where GuidField is null)
   if @strID is null    break
   update <tablename> 
         set GuidField = CONVERT(uniqueidentifier, @ID)
         where ID = @strID
end

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Rob GaudetAuthor Commented:
Sorry, this is still not working. Can someone help out? Just not sure...
0
 
DcpKingCommented:
I'm sorry: I offered a solution on the tenth and you say "Sorry, this is still not working.", but give no details of why.

My offering assumes a table with a field ID that has a value suitable for converting into a GUID, and an empty GUID field. It loops through the rows filling the GUIG field from the value in the ID field.

It's meant as an example of how to convert values into GUIDs.

Your initial code example used:

select cast(ID as uniqueidentifier)
and
cast('71479D27-D7A2-4439-99CB-0B536DCEFB45' as uniqueidentifier)

but you should have used CONVERT (and not CAST), as I tried to show in my examples.

Could you please show what you have at the moment and what your current error message(s) are.
0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
How can I loop through a recordset putting this solution into place for each row from the source to the final table?


Here is a work around:

insert into t_guid (id,guidcol)
select Id, convert(uniqueidentifier,nvarcharcol) from t_nv

Open in new window


table structures:

CREATE TABLE [dbo].[t_guid](
	[id] [int] NULL,
	[guidCol] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[t_nv](
	[ID] [int] NULL,
	[nvarcharCol] [nvarchar](50) NULL
) ON [PRIMARY]

Open in new window


I hope it helps!
0
 
Rob GaudetAuthor Commented:
Let me re-explain the problem more clearly.


I need to copy 3000 rows from SourceTable in SQL Server 2008 into DestinationTable in the same database.

There are 3 issues.
1) Converting NVarchar from SourceTable table to UniqueIdentifier
2) Converting Text to UniqueIdentifier
3) Iterating through the records when run


insert into DestinationTable
(
    AdId (UniqueIdentifier), 
    BusinessId (UniqueIdentifier),
    AdSize (Varchar)
)
select 
    Convert( ID as uniqueidentifier ), 
    Convert( '71479D27-D7A2-4439-99CB-0B536DCEFB45' as uniqueidentifier ), 
    AdSize
from SourceTable

Open in new window


Does this help to clear up the problems?
0
 
Rob GaudetAuthor Commented:
This is the latest error..


(1 row(s) affected)

(1 row(s) affected)
Msg 8169, Level 16, State 2, Line 7
Conversion failed when converting from a character string to uniqueidentifier.


...and the code I tried.

DECLARE @DayCycleId nvarchar(max) = N'71479D27-D7A2-4439-99CB-0B536DCEFB45';
SELECT @DayCycleId, CONVERT(uniqueidentifier, @DayCycleId) AS 'New GUID Value';

DECLARE @CreatedBy nvarchar(max) = N'49493D4F-A0B0-43A5-8B32-45572E4D7E43';
SELECT @CreatedBy, CONVERT(uniqueidentifier, @CreatedBy) AS 'New GUID Value';

insert into Tag 
(
    BusinessId, 
    AdImage, 
    AdSize, 
    DayCycleId, 
    CreatedOn, 
    IsActive, 
    Name, 
    CreatedBy
)
select 
     CONVERT(uniqueidentifier, ID), 
    [IMAGE], 
    '1',
    @DayCycleId, 
    GETDATE(), 
    1, 
    'NA', 
    @CreatedBy
from
[IMPORT]
 

Open in new window

0
 
DcpKingCommented:
Firstly, you missed some of what I wrote in my earlier posts. The syntax of CONVERT is not the same as for CAST, so writing this will lead to a syntax error:

Convert( '71479D27-D7A2-4439-99CB-0B536DCEFB45' as uniqueidentifier )

You should, instead, write this:

select Convert( uniqueidentifier, '71479D27-D7A2-4439-99CB-0B536DCEFB45' )

which yields '71479D27-D7A2-4439-99CB-0B536DCEFB45'

The other thing that you may be getting problems with is that the hyphens MUST be in the right places!
'71479D27-D7A2-4439-99CB-0B536DCEFB45' will work,
'71479D27D7A2443999CB0B536DCEFB45'     won't work!

hth

Mike
0
 
Rob GaudetAuthor Commented:
This is what the Guid value looks like in the source table (4f4cc611-2382-42ba-b423-0008ac422ebf) it seems to have the right UniqueIdentifier format.

I modified the SELECT CONVERT as advised... got this..


(1 row(s) affected)

(1 row(s) affected)
Msg 8169, Level 16, State 2, Line 7
Conversion failed when converting from a character string to uniqueidentifier.

This is the SQL code I'm running.

DECLARE @DayCycleId nvarchar(max) = N'71479D27-D7A2-4439-99CB-0B536DCEFB45'
SELECT CONVERT(uniqueidentifier, @DayCycleId)

DECLARE @CreatedBy nvarchar(max) = N'49493D4F-A0B0-43A5-8B32-45572E4D7E43'
SELECT CONVERT(uniqueidentifier, @CreatedBy)

insert into Tag 
(
    BusinessId, 
    AdImage, 
    AdSize, 
    DayCycleId, 
    CreatedOn, 
    IsActive, 
    Name, 
    CreatedBy
)
select 
    CONVERT(uniqueidentifier, ID), 
    [IMAGE], 
    '1', 
    @DayCycleId, 
    GETDATE(), 
    1, 
    'NA', 
    @CreatedBy
from [IMPORT]

Open in new window

0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
1) Can you provide scripts for the tables "Tag"  and "Import"

2) Have you checked that you are getting values in @DayCycleId and @CreatedBy from the below script (partial script from your above post):

DECLARE @DayCycleId nvarchar(max) = N'71479D27-D7A2-4439-99CB-0B536DCEFB45'
SELECT CONVERT(uniqueidentifier, @DayCycleId)

DECLARE @CreatedBy nvarchar(max) = N'49493D4F-A0B0-43A5-8B32-45572E4D7E43'
SELECT CONVERT(uniqueidentifier, @CreatedBy)

Open in new window


check it by printing these values:

print @DayCycleId
print @CreatedBy

Open in new window

0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
Hi! try this one plz:

Added @DayCycleIdGuid and @CreatedByGuid :

DECLARE @DayCycleId nvarchar(max) 
DECLARE @DayCycleIdGuid uniqueIdentifier 
set @DayCycleId=N'71479D27-D7A2-4439-99CB-0B536DCEFB45'
SELECT @DayCycleIdGuid=CONVERT(uniqueidentifier, @DayCycleId)


DECLARE @CreatedBy nvarchar(max) 
DECLARE @CreatedByGuid uniqueIdentifier 
set @CreatedBy=N'49493D4F-A0B0-43A5-8B32-45572E4D7E43'
SELECT @CreatedByGuid=CONVERT(uniqueidentifier, @CreatedBy)


insert into Tag 
(
    BusinessId, 
    AdImage, 
    AdSize, 
    DayCycleId, 
    CreatedOn, 
    IsActive, 
    Name, 
    CreatedBy
)
select 
    CONVERT(uniqueidentifier, ID), 
    [IMAGE], 
    '1', 
    @DayCycleIdGuid, 
    GETDATE(), 
    1, 
    'NA', 
    @CreatedByGuid
from [IMPORT]

Open in new window

0
 
DcpKingCommented:
It looks like you have a problem with your values in ID (assuming it's a varchar(35) or wider).
71479D27-D7A2-4439-99CB-0B536DCEFB45, which works, has hyphens at positions 9, 14, 19, and 23. You might want to run a check on your source table - something like this:

select * from IMPORT
where substring(ID, 9, 1) != '-'
    or   substring(ID, 14, 1) != '-'
    or   substring(ID, 19, 1) != '-'
    or   substring(ID, 23, 1) != '-'

If anything pops out then you've probably found the cause of your problems ...

hth

Mike
0
 
Rob GaudetAuthor Commented:
Table schemas.

IMPORT -origin table

CREATE TABLE [dbo].[IMPORT](
	[WEBSITE] [nvarchar](255) NULL,
	[FACEBOOK] [nvarchar](255) NULL,
	[IMAGE] [nvarchar](255) NULL,
	[ID] [nvarchar](255) NULL,
	[F5] [nvarchar](255) NULL,
	[BusinessId] [uniqueidentifier] NULL
) ON [PRIMARY]

Open in new window


TAG -destination table

CREATE TABLE [dbo].[Tag](
	[TagId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[Name] [varchar](1000) NOT NULL,
	[Description] [varchar](5000) NULL,
	[BusinessId] [uniqueidentifier] NULL,
	[MapUrl] [varchar](1000) NULL,
	[CreatedOn] [datetime] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[CreatedBy] [uniqueidentifier] NOT NULL,
	[AdImage] [varchar](500) NULL,
	[AdSize] [varchar](2) NULL,
	[DayCycleId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED 
(
	[TagId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

0
 
Rob GaudetAuthor Commented:
Both table create scripts attached.
IMPORT.sql
TAG.sql
0
 
Rob GaudetAuthor Commented:
Mike, your query (below) returned no records, indicating that all of the id's are correct.

select * from IMPORT
where substring(ID, 9, 1) != '-'
    or   substring(ID, 14, 1) != '-' 
    or   substring(ID, 19, 1) != '-' 
    or   substring(ID, 24, 1) != '-' 

Open in new window

0
 
DcpKingCommented:
Well, it indicates that they all have hyphens in the right places, not that they are correct. F'instance, a string of 35 hyphens would pass that test! Still, it was an easy test that could have nabbed something.

Anyhow, now you've given us your structure for the table Tag we can see that your table has four, not three, uniqueidentifier fields - the three you see in your code, but another (TagID). I don't think you're telling it to populate automatically, though

Instead of defining TagID as

[TagId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,

you need to define it as

TagID UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWSEQUENTIALID()

hth

Mike
0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
Hi!

I have created the tables from the scripts you have provided and on my sample data it worked without issues, so it seems to be the case of some invalid data in your data.

use this script:

BEGIN TRY
DECLARE @id varchar(50)
DECLARE cur CURSOR  
LOCAL SCROLL STATIC
FOR
Select id FROM IMPORT
OPEN cur  
FETCH NEXT FROM cur
  INTO @id
  PRINT @id 
  select CONVERT(uniqueidentifier, @id) FROM [IMPORT]

WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM cur
  INTO @id
  PRINT @id 
  select CONVERT(uniqueidentifier, @id) FROM [IMPORT] 

END
END TRY
BEGIN CATCH
     print  ERROR_MESSAGE();
     print  ERROR_NUMBER();
     print  ERROR_LINE();
     print  ERROR_MESSAGE();
END CATCH
CLOSE cur  
DEALLOCATE cur 

Open in new window



Run this script and see results. it will display the valid ids just before the failure one. show us that id which is in error.
0
 
Rob GaudetAuthor Commented:
Shahan,

That query returned each record once for each row, over and over again... I've attached an image showing it.

I also attached a list of all of the ID's that i'm trying to import, these are the ones that have been indicated to have a problem somewhere....

Thanks guys for the help.
tables.gif
IMPORT-ID-COLUMN.csv
0
 
Rob GaudetAuthor Commented:
Mike, I'm trying your last suggestion now, you seem to be suggesting that I need to change the primary id TagId column properties?
0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
I have checked excel file and found that you have "NULL" at cell "A1629".


So you should do something like this:

CONVERT(uniqueidentifier, isnull(ID,newid()))

This is the replacement of line 19 of your this post ID: 38411144

that is, if there is a null in ID column take newid() instead for insertion.
0
 
Rob GaudetAuthor Commented:
Tried it Shahan.. and got the same error (below).


(1 row(s) affected)

(1 row(s) affected)
71479D27-D7A2-4439-99CB-0B536DCEFB45
49493D4F-A0B0-43A5-8B32-45572E4D7E43
Msg 8169, Level 16, State 2, Line 10
Conversion failed when converting from a character string to uniqueidentifier.

DECLARE @DayCycleId nvarchar(max) = N'71479D27-D7A2-4439-99CB-0B536DCEFB45'
SELECT CONVERT(uniqueidentifier, @DayCycleId)

DECLARE @CreatedBy nvarchar(max) = N'49493D4F-A0B0-43A5-8B32-45572E4D7E43'
SELECT CONVERT(uniqueidentifier, @CreatedBy)

print @DayCycleId
print @CreatedBy

insert into Tag 
(
    BusinessId, 
    AdImage, 
    AdSize, 
    DayCycleId, 
    CreatedOn, 
    IsActive, 
    Name, 
    CreatedBy
)
select CONVERT(uniqueidentifier, isnull(ID,newid())), 
    [IMAGE], 
    '1', 
    @DayCycleId, 
    GETDATE(), 
    1, 
    'NA', 
    @CreatedBy
from [IMPORT]

Open in new window

0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
check this out:

DECLARE @DayCycleId nvarchar(max) = N'71479D27-D7A2-4439-99CB-0B536DCEFB45'
SELECT CONVERT(uniqueidentifier, @DayCycleId)

DECLARE @CreatedBy nvarchar(max) = N'49493D4F-A0B0-43A5-8B32-45572E4D7E43'
SELECT CONVERT(uniqueidentifier, @CreatedBy)

insert into Tag 
(
    BusinessId, 
    AdImage, 
    AdSize, 
    DayCycleId, 
    CreatedOn, 
    IsActive, 
    Name, 
    CreatedBy
)
select 
    CONVERT(uniqueidentifier, isnull(ID,newid())), 
    [IMAGE], 
    '1', 
    @DayCycleId, 
    GETDATE(), 
    1, 
    'NA', 
    @CreatedBy
from [IMPORT]

Open in new window

0
 
Rob GaudetAuthor Commented:
Drat, same error again running that Shahan.



(1 row(s) affected)

(1 row(s) affected)
Msg 8169, Level 16, State 2, Line 7
Conversion failed when converting from a character string to uniqueidentifier.
0
 
DcpKingCommented:
Robert,
looking at your original code, you're not inserting anything into TagID, although you have it defined as not null:

[TagId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,

It's for this reason I'm suggesting that you should have it auto-fill (rather like an identity column). Of course, I may have misunderstood your code ...

Mike
0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
what if:
CONVERT(uniqueidentifier, isnull( NULLIF(ID,'') ,newid()   )

Open in new window

0
 
Rob GaudetAuthor Commented:
nope same error every time

Msg 8169, Level 16, State 2, Line 7
Conversion failed when converting from a character string to uniqueidentifier.
0
 
Rob GaudetAuthor Commented:
If I remove the BusinessId as part of the insert, everything works. So there is def a problem converting the text I have to Guid. I wonder if it has to do with the source field being nvarchar(255)? As I recall, nvarchar reserves the total space allocated whether it is used or not vs varchar that only uses the actual characters in the field??
0
 
Rob GaudetAuthor Commented:
GOT IT! Found one row that had erroneous data in it. Thanks for the persistent help everyone.
0
 
DcpKingCommented:
Happy to have helped get you there!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 15
  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now