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.

Answer 1

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

Answer 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

Answer 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!