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.
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:
This is not possible. The
Take note of the
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:
Besides code brevity and atomicity, I wouldn't see any other reason that you'd want to combine multiple destination tables into a single
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:
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
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.