Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

closed as off-topic by RolandoMySQLDBA, Paul White, Kin, Max Vernon, Mark Storey-Smith Jun 25 at 21:41

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RolandoMySQLDBA, Paul White, Kin, Max Vernon, Mark Storey-Smith
If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers 2

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

[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
{
        [ 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>
        | DEFAULT VALUES 
        }
    }
}
[;]

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.

share|improve this answer
    
Just a note - if you have a few that accesses two tables and you try to insert into the view, you can only specify the columns for one table at a time. Same reasoning as above. –  Alf47 Jun 25 at 13:11

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:

BEGIN TRANSACTION
INSERT INTO <table1> ...
INSERT INTO <table1> ...
COMMIT TRANSACTION

The official documentation for this is at http://msdn.microsoft.com/en-us/library/ms174377.aspx 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" (http://msdn.microsoft.com/en-gb/library/ms188792.aspx) & 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 (http://msdn.microsoft.com/en-us/library/ms175521.aspx) - 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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.