Insert 2 rows in tables with foreign key relation without knowing the primary key of the main table

Question

I have the following databasescheme in SQL Server Manager 2014.

Database scheme

I'm making a C#-windows application in Visual Studio and I want to insert a new orderline and a new order. The problem is that the primary keys of both tables, auto-generate in server manager, so I haven't yet the value of the primary key of the order-table, but I need that value to fill into the foreign key of the orderLine column. How can I insert these two rows.

Kind regards

What are you using to perform your inserts? Generally, you'll have to insert the Order, receive the new ID back, then insert the OrderLine. — jtimperley, Aug 08 '14 at 21:02
You can never insert into two tables in one statement. Maybe you could come up with a stored procedure that does the inserts one at a time. — Andrew, Aug 08 '14 at 21:03
@jtimperley, how can I receive my new Id when I don't have an Id or Unique row to search? — Programmer1994, Aug 08 '14 at 21:10
@Andrew with at once, I meant with 1 c# method, I edited that part. — Programmer1994, Aug 08 '14 at 21:12
@ComputerDude you should never have to search for new IDs, they are provided by SQL Server either as SCOPE_IDENTITY() for single operations or by adding 'OUTPUT insert.OrderId' to bulk operations. A more specific approach requires knowing which of the 100 .NET data access methods you've chosen. — jtimperley, Aug 08 '14 at 21:16

score 5 · Accepted Answer · answered Aug 08 '14 at 21:08

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 newOrderID;
    var cmd = "INSERT INTO Order (column_name) VALUES (@Value) ;SELECT CAST(scope_identity() AS int)";
    using (var insertCommand = new SqlCommand(cmd, con)) {
        insertCommand.Parameters.AddWithValue("@Value", "bar");
        con.Open();
        newOrderID = (int)insertCommand.ExecuteScalar();
    }
}

This will allow you to catch the last generated OrderId and use it in the Insert Statement for the OrderLine table.

Another option is to use the following SQL code:

string command = "INSERT INTO Order(totalPrice) OUTPUT INSERTED.ID VALUES(@totalPrice)" // this will be a parameter from your code

Then the OrderId can be taken from :

Int32 orderId = (Int32) command.ExecuteScalar();

Anybody any idea why I've been downvoted? Was my question unclear? — Programmer1994, Aug 08 '14 at 21:41

score 1 · Answer 2 · edited Jul 13 '16 at 08:01

If you're using any form of direct SQL, you need to receive the SCOPE_IDENTITY() value immediately after inserting your order, then use that value to insert your lines.

INSERT INTO Order
SELECT SCOPE_IDENTITY() AS NewId; OR RETURN SCOPE_IDENTITY(); OR DECLARE @OrderId INT = SCOPE_IDENTITY();
INSERT INTO OrderLine

Otherwise, use Entity Framework and it will automatically retrieve your new IDs and assign to dependencies.

score 1 · Answer 3 · edited May 23 '17 at 12:14

While scope_id() works fine for single rows, you really should learn to use the output clause as scope_id() is useless for multiple rows inserted with a single sql statement.

See this prior question for a simple example of using the output clause.

Obviously this allows you to retrieve more than just the identity value too.

ADDED

Also useful is the new sequence feature (added for 2012) instead of using identity. If your are coming from other databases this may seem a more natural solution.

Sequence is very useful if you would like to share a single sequence among several tables -- although this is an uncommon design I have used it a few times.

Insert 2 rows in tables with foreign key relation without knowing the primary key of the main table

3 Answers3