CODEWITHSUNDEEP
sql-servert-sql
user12116884
user12116884

Reputation:

What is the uniqueidentifier (default newid()) was inserted?

I have a table with a uniqueidentifier and NEWID() default for new records. Executed the insert script. How do I know what uniqueidentifier was generated for the Id column since the last insert?

Table Script

CREATE TABLE [dbo].[MyData](
    [Id] [uniqueidentifier] NOT NULL,
    [Data] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MyData] ADD  CONSTRAINT [DF_MyData_Id]  DEFAULT (newid()) FOR [Id]
GO

Insert Script

INSERT INTO dbo.MyData (Data)
VALUES (NULL)
GO

What is the uniqueidentifier was inserted?

Upvotes: 0

Views: 510

Answers (2)

Martin Smith
Martin Smith

Reputation: 452988

There is no particular value in relying on the default in your example.

Just create a scalar variable of type uniqueidentifier and assign it the result of NEWID yourself

DECLARE @Id UNIQUEIDENTIFIER = NEWID();

INSERT INTO dbo.MyData (Id)
VALUES (@Id);

This is more concise than having to insert into a table variable and subsequently select from it.

Upvotes: 0

Thom A
Thom A

Reputation: 95554

Use an OUTPUT clause. I INSERT the data into a table variable so that is it can consumed by other statements afterwards:

DECLARE @IDs table (ID uniqueidentifier);

INSERT INTO dbo.MyData
OUTPUT inserted.Id
INTO @IDs
DEFAULT VALUES;

SELECT *
FROM @IDs;

Upvotes: 2

Related Questions