Create custom defined unique identifier

Question

I have a table with uniqueidentifier column. Insert script looks like this :

INSERT INTO [Users] ([UserId], [Name], [Surname]) VALUES (NEWID(), 'SomeName', 'SomeSurname')

NEWID() creates new GUID, which is inserted to table. I would like to create some data with defined id column, not automatically generated. E.g. 'a0000000-0000-0000-0000-000000000000'. This code doesn't work because it throws error 'String or binary data would be truncated.' :

INSERT INTO [Users] ([UserId], [Name], [Surname]) VALUES ('a0000000-0000-0000-0000-000000000000', 'SomeName', 'SomeSurname')

I tried cast this custom guid, but I was not successful as well.

INSERT INTO [Users] ([UserId], [Name], [Surname]) VALUES (CAST('a0000000-0000-0000-0000-000000000000' AS uniqueidentifier), 'SomeName', 'SomeSurname')

Do you have any clue how I can solve inserting data with custom defined ID?

have you looked at this: http://stackoverflow.com/questions/1390109/convert-varchar-to-uniqueidentifier-in-sql-server ? — Jester, Sep 22 '14 at 12:08
Are you sure the field userid is a unqiueidentifier field, your example "guid" inserts just fine into a unqiueidentifier field. — Ben Robinson, Sep 22 '14 at 12:17
@BenRobinson You're right. This error was caused by another column — y0j0, Sep 22 '14 at 12:26

score 1 · Answer 1 · answered Sep 22 '14 at 12:13

You don't have to cast it. SQL Server will do it for you.

This code works well:

create table x
( a uniqueidentifier
);

insert into x values ('A0000000-0000-0000-0000-000000000000')
;

insert into x values ('BBE46A77-3518-40E4-9B77-3275F3531B8B')
;

select *
from x
;

Create custom defined unique identifier

1 Answers1