April 3, 2015 at 8:42 pm
I have the following insert statement:
INSERT INTO [User].User_Profile
(UniqueId, Username, EmailAddress,
Password, BirthDay, BirthMonth,
BirthYear, Age, AccountType, DateCreated,
DeletedDate, DeletedReason, ProfileStatus)
VALUES
(NEWID(), @Username, @EmailAddress,
@Password, @BirthDay, @BirthMonth,
@BirthYeat, @Age, 1, SYSDATETIME(),
null, null, 2)
SELECT @@IDENTITY
As you can I have a uniqueidentifier (UniqueId) column which I populate with NewID() I'm trying to return this as I need it for other functionality of the website but I can't figure out how I can get it after the insert has completed?
April 4, 2015 at 12:17 am
Quick thought, use the OUTPUT clause, here is an example
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @GUIDINSERT TABLE
(
GUID_ID UNIQUEIDENTIFIER NOT NULL
,OTHER_VALUE INT NOT NULL
);
DECLARE @GOT_INSERTED_VALUES TABLE
(
GUID_ID UNIQUEIDENTIFIER NOT NULL
,OTHER_VALUE INT NOT NULL
);
INSERT INTO @GUIDINSERT(GUID_ID,OTHER_VALUE)
OUTPUT inserted.GUID_ID,inserted.OTHER_VALUE
INTO @GOT_INSERTED_VALUES
VALUES (NEWID(), 1)
,(NEWID(), 10)
,(NEWID(), 100)
,(NEWID(), 1000)
,(NEWID(),10000);
SELECT
G.GUID_ID
,G.OTHER_VALUE
FROM @GUIDINSERT G
SELECT
GI.GUID_ID
,GI.OTHER_VALUE
FROM @GOT_INSERTED_VALUES GI
Results (same for both queries)
GUID_ID OTHER_VALUE
------------------------------------ -----------
026297D3-99C0-49BF-A9E3-D039FD72B0C2 1
33DDC1FF-B079-4AF0-8652-B65F539A610B 10
BD79A40E-0F69-488B-991E-56EF42BB4CD6 100
B6B94EB7-DBAD-4B3E-A075-9403C82C5F6E 1000
CC0DAACE-CA48-45AD-B9D4-5939DCF71EBD 10000
April 4, 2015 at 3:15 am
Another vote for the OUTPUT clause. That link is to the documentation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2015 at 3:32 am
Grant Fritchey (4/4/2015)
Another vote for the OUTPUT clause. That link is to the documentation.
Thanks Grant, came back to add the documentation link:-)
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply