How Can I Use Standard SQL to Insert Data from One Table into Another?

Standard SQL for Copying Data Between Tables

Moving data between database tables requires consistent SQL syntax across various database systems. This article demonstrates a standard SQL approach for this common task.

The SQL-92 standard offers a simple and portable method for inserting data from one table into another:

Syntax:

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

Explanation:

• INSERT INTO target_table (column1, column2, ...): Specifies the destination table and the columns to receive the data.
• SELECT source_column1, source_column2, ...: Selects the data from the source table. The number and data types of selected columns must match the target columns.
• FROM source_table: Identifies the source table.
• WHERE condition: (Optional) Filters the data selected from the source table. Only rows meeting the condition are inserted.

Advantages:

• Portability: Works across numerous database management systems (DBMS).
• Simplicity: Easy to understand and implement.
• Database Independence: Eliminates the need for vendor-specific SQL commands.

This standard SQL approach is supported by a wide range of DBMSs, including:

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

This standardized method ensures efficient and consistent data transfer between tables regardless of the underlying database technology.

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