By Neko


2012-02-16 21:43:10 8 Comments

Using C# in Visual Studio, I'm inserting a row into a table like this:

INSERT INTO foo (column_name)
VALUES ('bar')

I want to do something like this, but I don't know the correct syntax:

INSERT INTO foo (column_name)
VALUES ('bar')
RETURNING foo_id

This would return the foo_id column from the newly inserted row.

Furthermore, even if I find the correct syntax for this, I have another problem: I have SqlDataReader and SqlDataAdapter at my disposal. As far as I know, the former is for reading data, the second is for manipulating data. When inserting a row with a return statement, I am both manipulating and reading data, so I'm not sure what to use. Maybe there's something entirely different I should use for this?

3 comments

@Tim Schmelter 2012-02-16 21:49:19

SCOPE_IDENTITY returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

You can use SqlCommand.ExecuteScalar to execute the insert command and retrieve the new ID in one query.

using (var con = new SqlConnection(ConnectionString)) {
    int newID;
    var cmd = "INSERT INTO foo (column_name)VALUES (@Value);SELECT CAST(scope_identity() AS int)";
    using (var insertCommand = new SqlCommand(cmd, con)) {
        insertCommand.Parameters.AddWithValue("@Value", "bar");
        con.Open();
        newID = (int)insertCommand.ExecuteScalar();
    }
}

@WhySoSerious 2014-01-09 06:44:39

what a great example Tim, helped me a lot. Thanks for posting a question like this Neko, exactly what I was looking for!

@Shimon Doodkin 2015-01-15 21:49:58

made me an error characters found after end of sql statement

@Jesse 2012-02-16 21:54:52

I think you can use @@IDENTITY for this, but I think there's some special rules/restrictions around it?

using (var con = new SqlConnection("connection string"))
{
    con.Open();
    string query = "INSERT INTO table (column) VALUES (@value)";

    var command = new SqlCommand(query, con);
    command.Parameters.Add("@value", value);
    command.ExecuteNonQuery();

    command.Parameters.Clear();
    command.CommandText = "SELECT @@IDENTITY";

    int identity = Convert.ToInt32(command.ExecuteScalar());
}

@KM. 2012-02-16 21:47:18

try this:

INSERT INTO foo (column_name)
OUTPUT INSERTED.column_name,column_name,...
VALUES ('bar')

OUTPUT can return a result set (among other things), see: OUTPUT Clause (Transact-SQL). Also, if you insert multiple values (INSERT SELECT) this method will return one row per inserted row, where other methods will only return info on the last row.

working example:

declare @YourTable table (YourID int identity(1,1), YourCol1 varchar(5))

INSERT INTO @YourTable (YourCol1)
OUTPUT INSERTED.YourID
VALUES ('Bar')

OUTPUT:

YourID
-----------
1

(1 row(s) affected)

@lamarant 2013-06-24 15:48:52

This works. I never knew about OUTPUT until now. Thanks!

@Louis Somers 2014-07-24 12:53:35

This does not work when there are Triggers on the table. In that case you will get the following error: The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. Not sure what they mean with without INTO clause though, since my INSERT statement does have an INTO clause...

@KM. 2014-07-24 13:07:11

@Louis Somers, just an plain OUTPUT clause will return a result set. If you add an INTO it will be inserted into a table. Create a table variable: DECLARE @Temp table (xyz int, abc varchar(5)) and you can have the OUTPUT rows go into this table, then after the INSERT you can just SELECT the @Temp table to return a result set. Use something like this: OUTPUT INSERTED.col1, INSERTED.col2 INTO @Temp then after the INSERT something like:` ;SELECT * FROM @Temp`.

Related Questions

Sponsored Content

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

21 Answered Questions

[SOLVED] What is the best way to give a C# auto-property an initial value?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

14 Answered Questions

[SOLVED] How do I return multiple values from a function?

47 Answered Questions

29 Answered Questions

37 Answered Questions

28 Answered Questions

[SOLVED] Get int value from enum in C#

  • 2009-06-03 06:46:39
  • jim
  • 1390821 View
  • 1668 Score
  • 28 Answer
  • Tags:   c# enums casting int

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

12 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Sponsored Content