how can I get uniqueidentifier value as return after insert a record

Question

Post reply

how can I get uniqueidentifier value as return after insert a record

dastagiri16

Hall of Fame

Points: 3544
More actions
January 17, 2010 at 11:11 pm

#218292

Hi...
how can I get uniqueidentifier value as return after insert a record.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gayathri.Varadarajan SSCrazy Points: 2080 More actions · Answer 1

Hi

Try making use of the output clause.

Eg:

create table tab (i uniqueidentifier,j int)

declare @tab table (i uniqueidentifier,j int)

insert into tab

output inserted.i ,inserted.j into @tab

values(NEWID(),1)

select * from @tab

arun.sas SSChampion Points: 11831 More actions · Answer 2

Hi,

use the trigger to get the newid value

create table MYTABLE

(

col1 int,

col2 varchar (2)

)

CREATE trigger ins_trg_MYTABLE on MYTABLE

for INSERT

as

PRINT CONVERT(varchar(255), newid())

return

insert into MYTABLE

select 1,'A'

Gayathri.Varadarajan SSCrazy Points: 2080 More actions · Answer 3

Hi

I am trying to understand your scenario...

Why do you require the newid() to be printed in the trigger?

arun.sas SSChampion Points: 11831 More actions · Answer 4

Hi,

Because the OP needs to get the unique identifier in every row insert!!!

nigel. SSChampion Points: 11636 More actions · Answer 5

It depends on a lot of things, please post more info on exactly what you are trying to achieve including table structures, example data.

Do you want to use a stored procedure?

Do you want the new id returned in an output parameter or as a result set?

This is just one way using a stored proc:

CREATE TABLE testTable1

(

id uniqueidentifier,

name varchar(50)

)

GO

CREATE PROCEDURE procTestTableInsert

(

@name varchar(50)

)

AS

DECLARE @id uniqueidentifier

SELECT @id = NEWID()-- Get a new unique identifier to insert

INSERT

testTable1(id,name)

VALUES

(@id,@name)

SELECT @id-- 'Return' the inserted uniqueidentifier as a 1 row 1 column result set

GO

--== Do the insert

EXEC procTestTableInsert 'Fred'

--== CLEAN UP

DROP TABLE testTable1

GO

DROP PROCEDURE procTestTableInsert

GO

Nigel
How to post questions

enhu819 SSC Rookie Points: 29 More actions · Answer 6

Try this:

DECLARE @id varchar(50) = CONVERT(VARCHAR(50), NEWID());

INSERT INTO [yourtable] ( [id]) VALUES (@id);

SELECT @id;

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 7

if you are wanting to return the identity of the insert perhaps to use as a foreign key in another insert

create table x1 (id int identity(1,1),col2 varchar(100))

declare @id int

insert into x1 select NEWID()
set @id=SCOPE_IDENTITY()

select @id

***The first step is always the hardest *******

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

dastagiri16 - Sunday, January 17, 2010 11:11 PM
Hi... how can I get uniqueidentifier value as return after insert a record.

Could you post a better question?

Are you looking for something like this?
CREATE TABLE [dbo].[Sample] ( SampleId UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY NONCLUSTERED , SampleData VARCHAR(32) );

DECLARE @PKeys TABLE (PKey UNIQUEIDENTIFIER);

INSERT INTO [dbo].[Sample]([SampleData])
OUTPUT [Inserted].[SampleId] INTO @PKeys([PKey])
VALUES ('Sample1'),('Sample2'),('Sample3');

SELECT * FROM [dbo].[Sample] AS ;
SELECT * FROM @PKeys AS [pk];

DROP TABLE [dbo].[Sample];

nigel. SSChampion Points: 11636 More actions · Answer 9

NB. This is an 8 year old post 🙂

Nigel
How to post questions