CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MarceloBarbosa
MarceloBarbosa

Reputation: 915

SQL Server : retrieve composite primary key after insert

I need to retrieve a composite primary key from my table after inserting new rows.

This is the table design:

enter image description here

This is my query

Set Nocount on

insert into AE_Movimenta (NumContrato, ANoMov, NumMov, CodTpDoc, CodNatOper, CodCondPag, TipoMov, CodTab, CodTpEntrega, Tipo, DtMov, CodUsu, CodSetor, DtDoc, NumDoc, Serie, IdNfe, NumIP, DtSistema, CodSistema) 
values (22, YEAR(GETDATE()), 15, 1,1,0,'S',0,1,'E',GETDATE(), 4, 1, GETDATE(), '', '','', '', GETDATE(), 1)

select NumMov = @@identity

set nocount off

And I get a NULL.

enter image description here

In this example, the NumMov primary key column is set manually, but I need to get this value dynamically after insert.

Upvotes: 1

Views: 2056

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

@@IDENTITY retrieves the last inserted identity value (subject to certain conditions about scoping). This is quite clearly documented here. So, if you have no column defined as an identity then nothing will be returned. This really has nothing to do with primary keys, except for the fact that it is good practice to use identity columns as primary keys.

If you want information about the last records inserted (or updated or deleted), then learn to use the output clause. This is the best way to get the data that was actually inserted (or updated or deleted) in the given statement.

Here is the documentation on the output clause.

Upvotes: 3

HLGEM
HLGEM

Reputation: 96552

You havea compostie key and so likely do not have an identiy value to retrieve..

What you need to use is the OUTPUT clause. Look up the syntax in Books online.

Upvotes: 3

Related Questions