SQL Return Inserted Data

Question

I want to select back the data I just inserted into the database. I know OUTPUT can be used and we can call INSERTED.[ColumnName] to grab the values that were inserted. Can these values be set to a variable of some kind or given an alias? I do not want to use a TEMP table because I wish to use variables if possible.

It would be nice for the solution to work when the PK is or is not an identity column.

EDIT: Contacts.PhoneNumberID is a foreign key to PhoneNumber.ID I want to do both insert statements back to back and I need the ID from PhoneNumber to use for the Contacts.PhoneNumberID

EDIT: [PhoneNumber].[ID] has a default specified in the database, that is how the ID is being set

Here is what I am looking for:

INSERT INTO [PhoneNumber] (Number) 
OUTPUT INSERTED.ID, INSERTED.Number 
VALUES ('555-555-5555') 

INSERT INTO [Contacts] (Name,PhoneNumberID) 
VALUES ('SomeName', {ID From previous insert})

Can we some how alias the insert statement to say:

INSERT INTO [PhoneNumber] (Number) 
OUTPUT INSERTED.ID, INSERTED.Number 
VALUES ('555-555-5555') as A

I know we cannot actually Alias an insert statement as done above, I am looking for the proper way to do this.

Then you could do this:

INSERT INTO [Contacts] (Name,PhoneNumberID) 
VALUES ('SomeName', A.ID)

I am going for a final result of something similar to this:

INSERT INTO [PhoneNumber] (Number) 
OUTPUT INSERTED.ID, INSERTED.Number 
VALUES ('555-555-5555') as A

INSERT INTO [Contacts] (Name,PhoneNumberID) 
VALUES ('SomeName', A.ID)

Reason I am trying to do this:

I wish to put both insert statements in a transaction block, if one of them fails then I can rollback and not commit anything.

I don't understand your question or what you are trying to do here. What does an alias have to do with anything? If you want multiple statements to be part of a transaction you simply add both statements inside the transaction. The best approach would be to wrap all that inside a try/catch block. — Sean Lange, Dec 08 '15 at 19:54
Apologies. I added an edit as to why I am trying to do this. It is all based on foreign keys and I need the PK from the first table to insert into the second table — Agrejus, Dec 08 '15 at 19:59
I know you mentioned trying to solve this without a `TEMP` table, however, [this solution](http://stackoverflow.com/a/10999467/3854195) using a table variable would work the way you've described. — Morpheus, Dec 08 '15 at 20:00
Thank you Morpheus! I did see that example and am hoping it can be done without a TEMP table — Agrejus, Dec 08 '15 at 20:02
How is [PhoneNumber].[ID] getting a value if it is not an Identity? — paparazzo, Dec 08 '15 at 21:00
There could be a default set for the [PhoneNumber].[ID] . I will update the question. Apologies for not specifying. — Agrejus, Dec 14 '15 at 15:22

Yuri · Accepted Answer · 2015-12-08T21:02:14.983

1

For this purpose you need to use IDENT_CURRENT('your_table) function. It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. So your code will look like this:

Declare @last_ident numeric(38,0)
INSERT INTO [PhoneNumber] (Number) 
VALUES ('555-555-5555') 

SELECT @last_ident = IDENT_CURRENT('PhoneNumber')

INSERT INTO [Contacts] (Name,PhoneNumberID) 
VALUES ('SomeName', @last_ident)

More information you can find here

EDIT:

If you need to get non Identity field you should use OUTPUT then

Declare @tbl table(ID int)
INSERT INTO [PhoneNumber] (Number) 
OUTPUT INSERTED.ID INTO @tbl
VALUES ('555-555-5555') 

Declare @id int
select @id = ID from @tbl

INSERT INTO [Contacts] (Name,PhoneNumberID) 
VALUES ('SomeName',@id)

edited Dec 08 '15 at 21:02

answered Dec 08 '15 at 20:28

Yuri

2,820
4
28
40

This is close, but it seems to only work if the PK is an Identity Column – Agrejus Dec 08 '15 at 20:35
PhoneNumber.ID is my PK. It work's great when ID is an Identity column, but if it is not then it just returns null. I revised my question above to state that I would like the possible answer to work when the PK is and is not an identity column. It's looking like a temp table is really the only way. – Agrejus Dec 08 '15 at 20:42
Instead of using #temp you can declare table variable DECLARE @tbl(PHONEID int) and then use your OUTPUT to collect result Inserted.ID – Yuri Dec 08 '15 at 20:48
Ah ha! This is almost exactly what I wanted! I didn't want to use a temp table because its such a small non complex amount of data I am selecting back – Agrejus Dec 08 '15 at 20:58
I will mark your question as answered if you can fix the minor syntax error you have. Declare @tbl(ID int) should be Declare @tbl table(ID int) – Agrejus Dec 08 '15 at 21:00
where is that problem? – Yuri Dec 08 '15 at 21:01
Just edited my comment. Thanks a million for your answer :) – Agrejus Dec 08 '15 at 21:01
My pleasure - happy coding :-) – Yuri Dec 08 '15 at 21:02
1

@user3329760 My comment on your question was a suggestion to use a table variable and I provided a link to an example. ;) – Morpheus Dec 08 '15 at 23:32
1

Morpheus I'm so sorry, you are 100% correct! Not sure how I missed that one >_ – Agrejus Dec 08 '15 at 23:48

score 0 · Answer 2 · answered Dec 08 '15 at 20:02

0

You can get the latest inserted from SCOPE_IDENTITY(). So your code could look like that:

DECLARE @id AS INT;
INSERT INTO [PhoneNumber] (Number) 
VALUES ('555-555-5555')

SELECT @id = SCOPE_IDENTITY();

INSERT INTO [Contacts] (Name,PhoneNumberID)
VALUES ('SomeName', @id)

For DECLARE @id AS INT; you need to change the type of the variable to fit the data type PhoneNumberID column

answered Dec 08 '15 at 20:02

Thomas

24,234
6
81
125

Would SCOPE_IDENTITY() work with multiple keys if present? – Agrejus Dec 08 '15 at 20:04
2

@user3329760 `SCOPE_IDENTITY`works only on a `IDENTITY` column. Therefore, it can only work on a single column key (the identity field must be the primary key) – SQL Police Dec 08 '15 at 20:15
Thank you Thomas. I've been going around and around on this one. I know about selecting @@IDENTITY or SCOPE_IDENTITY(), but as stated that's only for identity columns. Aside from a TEMP table I don't think there is any way to achieve what I am looking for. – Agrejus Dec 08 '15 at 20:18
2

@user3329760 **Attention:** `SCOPE_IDENTITY`can lead to problems in concurrent situations. Better to use `OUTPUT`. Please read here: http://stackoverflow.com/a/31238742/2504785 – SQL Police Dec 08 '15 at 20:19
These are two differents thing, SCOPE_IDENTITY only return the last inserted identity whereas OUTPUT will give you all the inserted data. – Thomas Dec 08 '15 at 20:29
1

Sorry @SeanLange this comment was for SQLPolice – Thomas Dec 08 '15 at 20:45
1

@SeanLange Please read here. `SCOPE_IDENTITY`is not really safe. http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/ – SQL Police Dec 08 '15 at 21:50
@SQLPolice, after reading the mentioned article, this is only valid for SQL Server 2008, the bug has been fixed for SQL Server 2008R2. Please read this article : [scope-identity-returning-incorrect-value-fixed](http://stackoverflow.com/questions/7562917/scope-identity-returning-incorrect-value-fixed) – Thomas Dec 09 '15 at 00:54

score 0 · Answer 3 · answered Dec 08 '15 at 21:05

0

You could also use Sequence.

Declare @newId int = NEXT VALUE FOR [PhoneNumber_Seq];
Insert into [PhoneNumber] (ID, Number)
values(@newId, '555-555-55555')

INSERT INTO [Contacts] (Name,PhoneNumberID)
VALUES ('SomeName', @newId)

answered Dec 08 '15 at 21:05

tolrahC

135
1
8

SQL Return Inserted Data

3 Answers3