CedricCornelis CedricCornelis - 1 year ago 64
SQL Question

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

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

Answer Source

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");
        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();