3

Following situation:

I have a ASP.NET website for create VM's in an ESX environment. The user can select some settings and click on a button to create the VM.

The Click-Event of this button checks the values and write it into an MS SQL Server. The table has an primary key (Integer, IDENTITY) which I don't need to insert (because IDENTITY).

But I need this primary key, because I redirect the user after the event to another page and this page needs the primary key for regular queries (send it with querystring).

Currently, I make a SELECT query direct after the INSERT INTO query and take the last entry. That works as long as only one user uses this page.

My question is:

Is it possible to recieve the IDENTITY primary key directly from the INSERT INTO query (like a return value from a function) ?

Solaflex
  • 1,382
  • 1
  • 13
  • 24
  • Are you using SQL Server or MySQL? `AUTO_INCREMENT` is MySQL terminology; SQL Server calls them `IDENTITY` columns – Pondlife Apr 20 '13 at 13:19
  • I use MS SQL, I'm sorry for this confusion, I learned both, MS SQL at the company and MySQL at school. You are right, its `IDENTITY` – Solaflex Apr 20 '13 at 13:26
  • Thanks for clarifying. It looks like you didn't state your real question, which according to your comment on Mitch's answer is "how do I return a new `IDENTITY` value using `ExecuteNonQuery`?" If that's correct, then see [this question](http://stackoverflow.com/questions/14246744/executescalar-vs-executenonquery-when-returning-an-identity-value). – Pondlife Apr 20 '13 at 13:30
  • It looks good, but I have to tried it out on monday at work. I'll report back on monday. – Solaflex Apr 20 '13 at 14:34

3 Answers3

6

Yes.

Use the OUTPUT clause

example from the MSDN link:

The following example inserts a row into the ScrapReason table and uses the OUTPUT clause to return the results of the statement to the @MyTableVar table variable. Because the ScrapReasonID column is defined with an IDENTITY property, a value is not specified in the INSERT statement for that column.

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

(Assuming you are using Sql Server)

-==========================

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Very interesting, but how can I use it in this syntax: "INSERT INTO VM (VMName, Memory) VALUES ('XXX',4);" ? – Solaflex Apr 20 '13 at 11:10
  • In a the same way as above. – Mitch Wheat Apr 20 '13 at 12:30
  • You missunderstand me and I was unclear. the SQL code is clear, but what is with the C# part. I work with [ExecuteNonQuery](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx) to write the queries into the table and this function can't return the primary key. – Solaflex Apr 20 '13 at 12:57
  • Use ExecuteQuery() and a DataReader to read the results, if multiple. OR insert into a temp variable, select a single result, and use Execute Scaler() – Mitch Wheat Apr 21 '13 at 01:09
2

Off the top of my head, @@IDENTITY is what you want here.

That is of course assuming you are using MS SQL server.

eg Insert into xxx.... ; select @@IDENTITY

EDIT:

As Mitch Wheat pointed out, @@SCOPE_IDENTITY is a better option than @@IDENTITY. This is because @@SCOPE_IDENTITY returns the ID in the current scope, whereas @@IDENTITY may return an ID created by a trigger or a UDF.

Chris Payne
  • 1,700
  • 1
  • 16
  • 33
  • 2
    SCOPE_IDENTITY() would be more correct. But it is unnecessay with OUTPUT clause. – Mitch Wheat Apr 20 '13 at 09:41
  • Yes, SCOPE_IDENTITY may be a safer idea, but as long as it's just a simple insert statement (with no triggers or functions) then I think both should work – Chris Payne Apr 20 '13 at 09:45
  • 2
    why risk it? use scope_identity(). But as I said, it is unnecessary if you use the OUTPUT clause – Mitch Wheat Apr 20 '13 at 09:46
  • Yes I'm agreeing that SCOPE_IDENTITY would be the safer one to use. I'll update my answer to reflect that. It's been a while since I've wrote much SQL (the projects I work on are all EF now), but when I did, I preferred using IDENTITY or SCOPE_IDENTITY. It's just a matter of opinion. – Chris Payne Apr 20 '13 at 09:55
  • 1
    It is not "just a matter of opinion"! : @@IDENTITY returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it. – Mitch Wheat Apr 20 '13 at 10:00
  • Thanks for the response. Question from my side, how can I use it together with [ExecuteNonQuery](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx) ? Because normly as far as I know, this function doesn't return strings (primary key can also be a string) – Solaflex Apr 20 '13 at 11:29
  • No, I mean using @@SCOPE_IDENTITY vs OUTPUT is a matter of opinion – Chris Payne Apr 20 '13 at 13:19
-1

I think you can also use Sequence for your problem.

Sequence automaticaly generate series of unique number which can be used as primary key.

Taryn
  • 242,637
  • 56
  • 362
  • 405
Avi
  • 1,115
  • 8
  • 20
  • 30