Start Free Trial

asked on

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.

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

ASKER

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)

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

ASKER

Sorry, this is still not working. Can someone help out? Just not sure...

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.

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!

ASKER

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?

ASKER

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

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

ASKER

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

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

ASKER

Both table create scripts attached.
IMPORT.sql
TAG.sql

ASKER

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

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

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.

ASKER

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

ASKER

Mike, I'm trying your last suggestion now, you seem to be suggesting that I need to change the primary id TagId column properties?

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.

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

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

what if:

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

Open in new window

ASKER

nope same error every time

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

ASKER

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??

ASKER

GOT IT! Found one row that had erroneous data in it. Thanks for the persistent help everyone.

Happy to have helped get you there!