Adding Data to Multiple Tables Using Single Query [closed]

Posted on

Question :

I want to add data to multiple tables using a single INSERT INTO query, can I do this?

Please guide me and provide me syntax if this is possible.

Answer :

This is not possible. The INSERT statement takes a single destination object as per the syntax (BOL reference):

[ WITH <common_table_expression> [ ,...n ] ]
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ] 
        | derived_table 
        | execute_statement
        | <dml_table_source>

Take note of the <object> placeholder on the 6th line of the syntax snippet.

Is there a particular reason why you want to insert into two tables in one statement? Perhaps you are looking for both inserts to either succeed or fail as one? In that case, you would be looking at explicit transactions:

begin tran;
    insert into dbo.YourTable1 ...
    insert into dbo.YourTable2 ...
commit tran;

Besides code brevity and atomicity, I wouldn’t see any other reason that you’d want to combine multiple destination tables into a single INSERT statement.

As Thomas points out, if your goal is to ensure that the operation is atomic (both inserts either work or fail in unison) then what you are looking for is transactions like so:

INSERT INTO <table1> ...
INSERT INTO <table1> ...

The official documentation for this is at but you would be better off finding a good book/tutorial if you are a beginner as that documentation is more geared towards reference and knowledge expansion use rather and initial learning. For extra complications see “XACT_ABORT” ( & other transaction behaviour controlling options, and look into TRY/CATCH (added in SQL2005) for more flexible exception handling.

You can insert/update multiple tables at once while looking like you are updating a simple single object by using views with INSTEAD OF triggers ( – essentially you define a view that merges the data from the tables as needed and create a trigger that handles the complications of insert and update operations (translating actions upon the view into actions upon the base tables (and potentially others)).

If you consider the individual stores in a partitioned tables to be separate entities then this can fit the description of a single operation on (what looks like) a single object affecting many tables at once.

This is of course fairly generic information: for something more specific you’ll need to describe much more precisely in your question what you are trying to achieve (and why, which may be relevant for suggesting alternatives). As it stands your question may be closed for being too vague.

Leave a Reply

Your email address will not be published. Required fields are marked *