SQL Server - Inserting Record To Many Tables At A Time

I have a stored proc that executes insert onto 4 tables on SQL Server database. These 4 tables have exactly the same structure. How can I perform the insert at a time?

Maybe something like:

INSERT INTO Table1, Table2, Table3, Table4
VALUES (@p1, @p2, ....... @pn)
开发者_StackOverflow中文版

There isnt a way - this is four separate inserts. You could put all results into a single temp table and then select into the other tables to reduce the code, but you cannot do all four at once.

See: Transact-sql insert in two tables at once?

You can't insert into multiple tables in a single INSERT statement. To ensure the insert into all four tables is atomic, you wrap the inserts in a transaction. Pseudocode:

BEGIN TRANSACTION

  INSERT INTO Table1(...) VALUES(...)

  INSERT INTO Table2(...) VALUES(...)

  INSERT INTO Table3(...) VALUES(...)

  INSERT INTO Table4(...) VALUES(...)

COMMIT

You just need to separate them, per say they cant be combined into one statement but this would work equally as well.

INSERT INTO Table1
VALUES (@p1, @p2)

INSERT INTO Table2
VALUES (@p1, @p2)

INSERT INTO Table3
VALUES (@p1, @p2)

INSERT INTO Table4
VALUES (@p1, @p2)

You could set up one table to be the "master" and INSERT only to it. You'd then have to create a trigger on that table to push the changes into any others which need the new rows. Other posters are correct - it's four INSERTs, but this could simplify client code. While making it confusing as well... =/