1

I need to create a set of data for our app. I am trying to get the iInserted ID to be printed in the messages panel, but nothing seems to work.

INSERT INTO ...

DECLARE @ID INTEGER = (SELECT @@Identity);
PRINT @ID;

The print is printing nothing at all...

How can I get the id.?

I tried with

DECLARE @ID INTEGER = (SELECT IDENT_CURRENT('tablename'))

and it didn't work either...

EDIT: I need to PRINT the ID in the message PANEL, I must not print a table.

Script:

INSERT INTO Employee (Name, Email)
VALUES ('a', 'a')

DECLARE @id INTEGER = ??
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vince
  • 1,279
  • 2
  • 20
  • 40
  • Possible duplicate of [Best way to get identity of inserted row?](https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) – ATC Feb 22 '18 at 18:55
  • i've checked it and it didn't help me to solve my probleme – Vince Feb 22 '18 at 18:57
  • Are you sure the record is actually inserted into the table? – ATC Feb 22 '18 at 19:00
  • Yes, i'm sure it is inserted – Vince Feb 22 '18 at 19:03
  • 1
    what version of SQL Server are you working with? Does the `Emplyee` table have an `identity` column? – Zohar Peled Feb 22 '18 at 19:41
  • Azure so i dont know which version exactly – Vince Feb 22 '18 at 20:20
  • No one reading your question knows what "message panel" means, but it is obviously some part of an application. So your question really isn't one about tsql so much as how to get information about the result of your insert statement into your application. And you did not mention anything about your app environment which is why you and everyone else is spinning wheels making tsql suggestions. – SMor Feb 22 '18 at 21:49
  • Message panel from Management Studio ? where they write (1 rows affected) How can i call it... when it's called Messages ? – Vince Feb 23 '18 at 13:48
  • Does your table have an identity column? – Zohar Peled Feb 25 '18 at 06:13

2 Answers2

2

Try using the OUTPUT clause with your insert statement, play with this and see if it helps. This is the more verbose way but it is scoped appropriately and if you are using parallelism is the only guaranteed way.

--Create table variable to hold the new IDcreated by insert statement.
DECLARE @Inserted TABLE (ID Int);

--Insert the new record.
INSERT INTO [Whatever].[Table] 
    (ID)
OUTPUT Inserted.ID INTO @Inserted 
VALUES
    (ID);

SELECT ID from @Inserted;
Nard Dog
  • 906
  • 1
  • 18
  • 33
  • What is Inserted? where does it come from? – Vince Feb 22 '18 at 19:00
  • You can't output into a scalar variable, only into a table (actual, temporary or variable doesn't matter) – ATC Feb 22 '18 at 19:01
  • Good catch, I forgot quickly about this rule, updated and this should work. – Nard Dog Feb 22 '18 at 19:12
  • Also, fyi just since you asked, inserted and deleted are special tables created by sql in DML statements you can use which hold the data that was just modified. You can access the entire row of data this way after the fact. – Nard Dog Feb 22 '18 at 19:16
  • i get this... Incorrect syntax near 'OUTPUT'. That's why i asked you where it come from... – Vince Feb 22 '18 at 19:23
2

If your table has an IDENTITY column, you should be using

DECLARE @ID INT = SCOPE_IDENTITY();

Since SCOPE_IDENTITY returns the last inserted identity in the scope you're running.

If you have an insert inside a trigger and use the @@IDENTITY variable, you'll have conflicts and you'll get a wrong IDENTITY value.

Pedro Martins
  • 854
  • 6
  • 9