How to Insert Data from One Table to Another Using Standard SQL Syntax?

SQL: Copying Data Between Tables

A frequent SQL task involves transferring data from one table to another. While specific syntax might differ slightly across database systems, a broadly compatible approach uses the INSERT INTO ... SELECT statement.

Standard SQL Syntax for Data Insertion

The following syntax provides a high degree of compatibility across various database management systems (DBMS):

<code class="language-sql">INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
[WHERE condition];</code>

Here's a breakdown:

• target_table: The table receiving the new data.
• column1, column2, ...: The columns in target_table where data will be inserted. The order must match the SELECT statement.
• source_column1, source_column2, ...: The columns in source_table providing the data. The data types should be compatible with the corresponding target_table columns.
• source_table: The table providing the data.
• WHERE condition (optional): Filters the data from source_table before insertion. Only rows satisfying the condition are copied.

DBMS Compatibility

This INSERT INTO ... SELECT method generally works across a wide range of DBMS, including:

• Oracle
• Microsoft SQL Server
• MySQL
• PostgreSQL
• SQLite
• Teradata
• DB2
• Sybase
• Vertica
• HSQLDB
• H2
• Amazon Redshift
• SAP HANA
• Google Cloud Spanner

Illustrative Example

Let's say we have an "employees" table and a "department_summary" table. To populate department_summary with employee department names:

<code class="language-sql">INSERT INTO department_summary (department_name)
SELECT department
FROM employees;</code>

This inserts all unique department names from the employees table into the department_name column of the department_summary table. Note that duplicate department names will only be inserted once. If you need to handle duplicates differently, you'll need to add additional logic (e.g., using GROUP BY and aggregate functions).

The above is the detailed content of How to Insert Data from One Table to Another Using Standard SQL Syntax?. For more information, please follow other related articles on the PHP Chinese website!