Inserting multiple rows in multiple table in a single sql query

Question

I want to some rows in a table , then select a specific data from the newly added record and then insert that data in another table.The following statement is obviously not working but it should give an idea of what i am trying to accomplish here.

cmd = new SqlCommand("INSERT INTO SalesDetails.sale(sale_date) VALUES (@sale_date); 
                      SELECT sale_id  FROM SalesDetails.sale WHERE sale_date=@sale_date; 
                      SELECT stock_id FROM StockDetails.stock_item WHERE item_id=@item_id;
                      INSERT INTO SalesDetails.payment(payment_method,sale_id) 
                      VALUES (@payment_method, sale_id); 
                      INSERT INTO SalesDetails.dispatch_out_item(stock_id,sale_id,qty) 
                      VALUES (stock_id,sale_id,@qty);", con);

It is not possible to achieve your request in one single SQL statement. Like @Matt Murdock said you should create a stored procedure which executes all steps in one Transaction. In order to select data from a current insert statement you can use the output keyword which is explaind here — Alex B.
– Alex B., Commented Dec 3, 2015 at 8:48

Dhrumil · Accepted Answer · 2015-12-03 08:19:10Z

Rather than writing eveything into one single SQL Command statement, I would suggest you to write a Stored Procedure for this.

The reason behind using a stored procedure is that you can more cleanly and nicely handle multiple table transactions in it while also implementing the Transaction logic through which you can ensure data consistency of all the tables where the changes will be taking place. Since you are manipulating multiple tables here, you need to make sure that the change is preserved in all tables or in none of them.

Check this link as a reference : Using Stored Procedures with Transactions

Hope this helps.

Collectives™ on Stack Overflow

Inserting multiple rows in multiple table in a single sql query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related