Which Command Should You Use To Copy Data From One Table And Have It Added To An Existing Table?

Copying Information Between Tables

Terminal modified: August 09, 2021

Re-create into a new pre-structured table:

                              CREATE                Tabular array                [                Tabular array                to                copy                To                ]                As                [                Tabular array                to                copy                From                ]                WITH                NO                Information                ;

- Note: "WITH NO DATA" specifies that the new tabular array should merely re-create the tabular array structure with no data

Re-create into pre-existing tabular array:

                              INSERT                INTO                [                Tabular array                to                re-create                To                ]                SELECT                [                Columns                to                Copy                ]                FROM                [                Tabular array                to                copy                From                ]                WHERE                [                Optional                Condition                ];

Copying information between tables is just as easy every bit querying information all the same it volition accept a chip longer to run than a normal query. Information technology tin be used to update an inventory, create a table that has different permissions than the original, and much more.

Example:

Take for instance a shopkeeper who needs to create a master list of all the items in his store to comport a store-broad audit. However the information he needs be in separate tables containing the inventories of each department:

List of available Tables

In order to create a main list that contains all of the shop'due south items and prices the shopkeeper needs to create the table for all items and re-create the data from each of the departments into the new table.

Creating the table

The shopkeeper needs to first brand a new table to incorporate the information. The master list needs to take the same table construction (columns, data-types, etc.). The easiest mode to create a table with the same table construction as a different tabular array is to use:

                              CREATE                TABLE                [                New                Table                ]                AS                Table                [                Former                Tabular array                ]                WITH                NO                DATA                ;

Once filled out, this command will create a new table with the same table structure, but without any data. The shopkeeper tin use this to create his master listing:

Creating a blank copy of the table

With this done, the shopkeeper at present has the following tables:

Tables that the shopkeeper has

INSERT INTO command

Now that the shopkeeper'due south master list has been created and structured, the information needs to be inserted into the table. This can be done using the INSERT command. This command inserts specified values into a specified tabular array. It is often used to insert unmarried values into tables by running the command as such:

Inserting an element manually

When using INSERT INTO with the VALUES command it is possible to add entries by manus, however a query can as well be used in place of the VALUES command. For example to copy all items from the table "hardware" to the table "masterlist" the post-obit query tin be run:

Inserting using a subquery to copy a table

This query uses a subquery to find all values in "hardware" and then adds them to the "masterlist". In society to copy data from all the tables, the shopkeeper tin can utilise Matrimony to merge the tables together in the subquery:

Using a subquery to copy multiple selections using "UNION"

This gives the shopkeeper the desired outcome so that he tin can begin his inspect:

The final resulting table from our previous insert

Adding Weather condition

Copying information with INSERT INTO can also be done with weather condition. For example, if the shopkeeper is just interested in items over $fifty these values can be copied by using:

                              INSERT                INTO                masterlist                [                SELECT                statements                ]                WHERE                toll                >                money                (                fifty                );

Each SELECT argument tin can also have its own where statement for table specific conditions. After the table is created and filled it can be manipulated, added to or removed from without affecting the tables the information was copied from.

Video example

Summary

To copy create a pre-structured table:
- CREATE TABLE [Table to copy To] Equally [Table to copy From] WITH NO DATA;
- Table will be pre structured to handle data from the 'table to copy from'
Re-create into pre-existing tabular array:
- INSERT INTO [Tabular array to copy To] SELECT [Columns to Copy] FROM [Tabular array to copy From] WHERE [Optional Condition];
Will create independent copy in the new tabular array