25

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.

Sam Keith
  • 339
  • 1
  • 5
  • 5

5 Answers5

39

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

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 7
    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. – Remus Rusanu Apr 12 '11 at 00:35
  • simple yet straight forward answer. – bot Feb 24 '16 at 10:28
7

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
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
5

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!

Jasel
  • 535
  • 8
  • 14
2

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

Chanderkant Sharma
  • 31
  • 2
2

You can try this. This worked for me.

Specify a length for VARCHAR when you cast/convert a value..for uniqueidentifier use VARCHAR(36) as below:

SELECT Convert (varchar(36),NEWID()) AS NEWID

The default length for VARCHAR datatype if we don't specify a length during CAST/CONVERT is 30..

Credit : Krishnakumar S

Reference : https://social.msdn.microsoft.com/Forums/en-US/fb24a153-f468-4e18-afb8-60ce90b55234/insufficient-result-space-to-convert-uniqueidentifier-value-to-char?forum=transactsql

Jayanth
  • 746
  • 6
  • 17
  • This resolved my issue, I was attempting pass a `@TraceId` parameter into SPROCS to enable information logging if actions were performed without error. Once I updated to varchar(36) my issue was resolved, however initially I had forgotten to update the top level paremeter `@TraceId output` to varchar(36) as well so it truncated the value initially in my logs. Don't forget to update all the references or you may have unexpected results lol. – nulltron Aug 04 '23 at 20:25