April 20, 2011 at 4:36 am
Hi,
I am getting error while inserting record in a uniqueidentifier column.
Create table script
CREATE TABLE xyz_test
([sp_key] [int] IDENTITY(1,1) NOT NULL,
[sp_guid] [uniqueidentifier] NOT NULL,
[sp_name] [nvarchar](250) NOT NULL,
[sp_org_level_name] [nvarchar](250) NULL,
[sp_category] [nvarchar](250) NULL,
[sp_status_cd] [nvarchar](250) NULL,
[sp_desc] [nvarchar](1000) NULL,
[sp_comment] [nvarchar](500) NULL,
[sp_eff_dt] [date] NOT NULL,
[sp_end_dt] [date] NOT NULL,
[sp_rec_ins_aud_key] [int] NOT NULL,
[sp_rec_upd_aud_key] [int] NOT NULL,
[sp_rec_active_ind] [bit] NOT NULL,
[sp_rec_change_rsn] [nvarchar](200) NULL,
CONSTRAINT [bk_xyz_test] UNIQUE CLUSTERED
(
[sp_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert into Script.
IF NOT EXISTS (SELECT *
FROM [dbo].xyz_test
WHERE [sp_key] = -1)
BEGIN
SET identity_insert [dbo].xyz_test ON;
INSERT INTO [dbo].xyz_test
([sp_key],
[sp_guid],
[sp_name],
[sp_org_level_name],
[sp_category],
[sp_status_cd],
[sp_desc],
[sp_comment],
[sp_eff_dt],
[sp_end_dt],
[sp_rec_ins_aud_key],
[sp_rec_upd_aud_key],
[sp_rec_active_ind],
[sp_rec_change_rsn])
VALUES (-1,
'00000000-0000-0000-000-00000000000',
'Unknown',
'Unknown',
'Unknown',
'Unknown',
'Unknown',
'Unknown',
'1900-01-01',
'1900-01-01',
-1,
-1,
1,
'Unknown');
SET identity_insert [dbo].xyz_test OFF;
END;
IF NOT EXISTS (SELECT *
FROM [dbo].xyz_test
WHERE [sp_key] = 0)
BEGIN
SET identity_insert [dbo].xyz_test ON;
INSERT INTO [dbo].xyz_test
([sp_key],
[sp_guid],
[sp_name],
[sp_org_level_name],
[sp_category],
[sp_status_cd],
[sp_desc],
[sp_comment],
[sp_eff_dt],
[sp_end_dt],
[sp_rec_ins_aud_key],
[sp_rec_upd_aud_key],
[sp_rec_active_ind],
[sp_rec_change_rsn])
VALUES (0,
'00000000-0000-0000-000-0000000000',
'NA',
'NA',
'NA',
'NA',
'NA',
'NA',
'1900-01-01',
'1900-01-01',
0,
0,
1,
'NA');
SET identity_insert [dbo].xyz_test OFF;
END;
Please suggest me the solution how to insert records in [sp_guid] column.
Regards,
Kiran R
April 20, 2011 at 5:03 am
It doesn't work because '00000000-0000-0000-000-0000000000' won't cast to a uniqueidentifier. Try this:
SELECT CAST('00000000-0000-0000-000-0000000000' AS uniqueidentifier)
SELECT CAST('00000000-0000-0000-0000-000000000000' AS uniqueidentifier)
Why do you want to put this value into a uniqueidentifier column? Why not use NEWID()?
...
VALUES (
-1,
NEWID(),
'Unknown',
...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 20, 2011 at 5:42 am
Thank It worked.
May 5, 2016 at 12:39 pm
ChrisM@home (4/20/2011)
It doesn't work because '00000000-0000-0000-000-0000000000' won't cast to a uniqueidentifier. Try this:SELECT CAST('00000000-0000-0000-000-0000000000' AS uniqueidentifier)
SELECT CAST('00000000-0000-0000-0000-000000000000' AS uniqueidentifier)
Why do you want to put this value into a uniqueidentifier column? Why not use NEWID()?
Using CAST() like this solved a similar issue I was working with. As to "Why not use NEWID()", because the existing application uses the same guid to link several different tables. Definitely poor design! And there were duplicate records in one of the those tables, another poor design decision. I had to delete the duplicated records, and re-insert (using the CAST()) just one record with the correct data. Note that the table does not have an identity column. Sigh!
May 5, 2016 at 4:23 pm
If you want a unique identifier with all zeroes, you can always do the following.
SELECT CAST(0x0 AS UNIQUEIDENTIFIER)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply