Is There a Universal SQL Syntax for Inserting Values from a Subquery?

Cross-Database SQL: Inserting Data from Subqueries

Moving data between tables is a fundamental database task. However, SQL syntax for this can differ significantly between database systems. This article explores a standardized approach for inserting values from a subquery, ensuring compatibility across various database engines.

A Universal SQL Solution

The ANSI SQL standard provides a consistent method for inserting data from a subquery:

<code class="language-sql">INSERT INTO table1 (column1)
SELECT col1
FROM table2;</code>

This approach is widely supported by numerous database systems, including:

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

Practical Example

To populate the column1 field in table1 with data from the col1 field of table2, use the following query:

<code class="language-sql">INSERT INTO table1 (column1)
SELECT col1
FROM table2;</code>

This efficiently creates new entries in table1, mirroring the col1 values from table2.

The above is the detailed content of Is There a Universal SQL Syntax for Inserting Values from a Subquery?. For more information, please follow other related articles on the PHP Chinese website!