Sql Server - Insufficient result space to convert uniqueidentifier value to char

Question

I am getting below error when I run sql query while copying data from one table to another,

Msg 8170, Level 16, State 2, Line 2 Insufficient result space to convert uniqueidentifier value to char.

My sql query is,

INSERT INTO dbo.cust_info (
uid,
first_name,
last_name
)
SELECT
NEWID(),
first_name,
last_name
FROM dbo.tmp_cust_info

My create table scripts are,

CREATE TABLE [dbo].[cust_info](
    [uid] [varchar](32) NOT NULL,
    [first_name] [varchar](100) NULL,
    [last_name] [varchar](100) NULL)

CREATE TABLE [dbo].[tmp_cust_info](
    [first_name] [varchar](100) NULL,
    [last_name] [varchar](100) NULL)

I am sure there is some problem with NEWID(), if i take out and replace it with some string it is working.

I appreciate any help. Thanks in advance.

Solution 1

A guid needs 36 characters (because of the dashes). You only provide a 32 character column. Not enough, hence the error.

Solution 2

You need to use one of 3 alternatives

1, A uniqueidentifier column, which stores it internally as 16 bytes. When you select from this column, it automatically renders it for display using the 8-4-4-4-12 format.

CREATE TABLE [dbo].[cust_info](
    [uid] uniqueidentifier NOT NULL,
    [first_name] [varchar](100) NULL,
    [last_name] [varchar](100) NULL)

2, not recommended Change the field to char(36) so that it fits the format, including dashes.

CREATE TABLE [dbo].[cust_info](
    [uid] char(36) NOT NULL,
    [first_name] [varchar](100) NULL,
    [last_name] [varchar](100) NULL)

3, not recommended Store it without the dashes, as just the 32-character components

INSERT INTO dbo.cust_info (
uid,
first_name,
last_name
)
SELECT
replace(NEWID(),'-',''),
first_name,
last_name
FROM dbo.tmp_cust_info

Solution 3

I received this error when I was trying to perform simple string concatenation on the GUID. Apparently a VARCHAR is not big enough.

I had to change:

SET @foo = 'Old GUID: {' + CONVERT(VARCHAR, @guid) + '}';

to:

SET @foo = 'Old GUID: {' + CONVERT(NVARCHAR(36), @guid) + '}';

...and all was good. Huge thanks to the prior answers on this one!

Solution 4

Increase length of your uid column from varchar(32) ->varchar(36) because guid take 36 characters Guid.NewGuid().ToString() -> 36 characters outputs: 12345678-1234-1234-1234-123456789abc

View more solutions

Comments

[Remus Rusanu]

And btw, you should use uniqueidentifier column type to store guids, not character(36) (var is no needed since is not variable, is it?). uniqueidentifier only needs 16 bytes for storage, as opposed to 36 needed for the character representation of a guid.

[bot]

simple yet straight forward answer.