The multitable INSERT statement is a variation on the INSERT statement syntax we’ve already seen. A multitable INSERT statement repeats the INTO clause of the INSERT statement to insert data into more than one table. Each INTO clause applies to just one table, but by repeating the INTO clause, you can add data to multiple tables. The multitable INSERT must have a subquery to select rows for inserting.

Multitable INSERT statements can accomplish a variety of tasks, including the following:

l            Extract data from one table, and insert the data into multiple tables with conditional logic, such as transforming data into a series of archive tables.

l            Exchange data between two similar systems of different requirements—perhaps between a transaction-based application and a data warehouse optimized for analysis.

l            Support logical archiving at any level of detail with logical decision points embedded in the INSERT statements.

l            Integrate complex queries with GROUP BY, HAVING, set operators, and more, all while moving any number of rows dynamically, distributing output into multiple data targets, and programming logical decision points to control data distribution.

l            Transform. data that is stored in rows and levels into a cross-tabulation output, the type you would typically see in a spreadsheet application.