CODEWITHSUNDEEP
c#sql-serversql-server-2008
user2650277
user2650277

Reputation: 6749

Inserting multiple rows in multiple table in a single sql query

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

Upvotes: 0

Views: 1738

Answers (1)

Dhrumil
Dhrumil

Reputation: 3204

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.

Upvotes: 2

Related Questions