Standard SQL INSERT INTO SELECT Syntax Guide

The standard SQL syntax for inserting values from one table into another that works across different database engines is INSERT INTO target_table[(column_list)] SELECT ... FROM ...;. This SQL-92 compliant syntax is supported by MySQL, Oracle, SQL Server, Informix, and DB2 with minimal or no modifications, making it the most portable solution for cross-database operations. The optional column list allows you to specify which columns to populate, making it flexible for various data migration scenarios.

Contents

• Basic INSERT INTO SELECT Syntax
• Column List Specification
• Cross-Database Compatibility
• Practical Examples
• Best Practices

Basic INSERT INTO SELECT Syntax

The fundamental SQL-92 syntax for inserting data from one table to another follows this structure:

INSERT INTO target_table
SELECT column1, column2, ...
FROM source_table
WHERE condition;

According to Stack Overflow, “This is supported in SQL-92 and works with minimal or no modifications in MySQL, PostgreSQL, SQL Server, Oracle, and DB2.”

The basic syntax includes:

• INSERT INTO target_table - specifies the destination table
• SELECT column1, column2, ... - selects columns from the source table
• FROM source_table - specifies the source table
• WHERE condition - optional filtering clause

This syntax is part of the SQL-92 standard, which was “a major revision of the standard” that introduced many features still used today Wikipedia.

Column List Specification

When you need to specify which columns to populate in the target table, you can include a column list:

INSERT INTO target_table (column1, column2, column3)
SELECT source_col1, source_col2, source_col3
FROM source_table;

As noted in DevTip, “Both the answers I see work fine in Informix specifically, and are basically standard SQL. That is, the notation: INSERT INTO target_table[(<column-list>)] SELECT ... FROM ...; works fine with Informix and, I would expect, all the DBMS.”

The column list specification allows you to:

• Select only specific columns from the source table
• Map columns from different data types or positions
• Skip columns that should retain default values
• Handle cases where source and target tables have different structures

Cross-Database Compatibility

The INSERT INTO SELECT syntax is remarkably consistent across major database systems, making it an excellent choice for cross-database applications.

Compatibility Matrix

As Tech Champion explains, “The main issue revolves around remembering the exact syntax, which varies between different SQL database management systems such as MySQL, Oracle, SQL Server, Informix, and DB2.” However, the standard INSERT INTO SELECT syntax remains consistent across these platforms.

Practical Examples

Here are some practical examples demonstrating the INSERT INTO SELECT syntax in action:

Example 1: Basic Data Copy

-- Copy all data from employees to employees_archive
INSERT INTO employees_archive
SELECT * FROM employees;

Example 2: Column List with Data Transformation

-- Copy specific columns with data transformation
INSERT INTO customer_summary (customer_id, full_name, registration_date)
SELECT 
    customer_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    CURRENT_TIMESTAMP AS registration_date
FROM customers
WHERE status = 'active';

Example 3: Conditional Data Insertion

-- Insert only records that meet certain conditions
INSERT INTO high_value_customers (customer_id, total_purchases, customer_name)
SELECT 
    c.customer_id,
    SUM(o.total_amount) AS total_purchases,
    c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.total_amount) > 10000;

Example 4: Cross-Database Table Creation

-- Create a new table by copying data from another table
INSERT INTO new_table (id, name, created_at)
SELECT 
    id,
    name,
    CURRENT_TIMESTAMP
FROM existing_table
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

These examples demonstrate how the standard SQL INSERT INTO SELECT syntax can be used for various data migration and transformation scenarios while maintaining compatibility across different database systems.

Best Practices

When using the INSERT INTO SELECT syntax across different database engines, consider these best practices:

1. Always Use Column Lists

Explicitly specify column lists rather than using SELECT * to ensure compatibility and prevent issues when table structures change.

-- Good practice
INSERT INTO target_table (col1, col2, col3)
SELECT source_col1, source_col2, source_col3
FROM source_table;

-- Avoid (may cause issues)
INSERT INTO target_table
SELECT * FROM source_table;

2. Handle Data Type Compatibility

Ensure that source and target columns have compatible data types to avoid conversion errors.

3. Use Transaction Blocks

Wrap INSERT INTO SELECT operations in transactions to ensure data consistency:

BEGIN TRANSACTION;
INSERT INTO target_table
SELECT * FROM source_table WHERE condition;
COMMIT TRANSACTION;

4. Consider Performance for Large Datasets

For large data migrations, consider:

• Using batch processing with LIMIT or TOP clauses
• Adding appropriate indexes to source and target tables
• Performing operations during off-peak hours

5. Test on Development First

Always test INSERT INTO SELECT statements on development or staging environments before executing against production data.

As the MySQL documentation suggests, “INSERT statement inserts one or more rows into an existing table. You can specify values explicitly, or select from another table.”

Conclusion

The standard SQL-92 syntax INSERT INTO target_table[(column_list)] SELECT ... FROM ...; provides a universal solution for inserting data from one table to another across different database engines. This syntax works consistently with MySQL, Oracle, SQL Server, Informix, and DB2, making it ideal for cross-database applications and data migration projects.

Key takeaways include:

• Use explicit column lists for better compatibility and maintainability
• The syntax is SQL-92 compliant and works across major database systems
• Always test your statements and consider performance implications
• Use transactions for data consistency during large operations

For developers working with multiple database systems, this standard syntax eliminates the need for database-specific workarounds and simplifies data management processes. Whether you’re performing simple data copies or complex transformations, the INSERT INTO SELECT syntax remains the most portable and reliable solution.

Sources

1. Stack Overflow - Insert into … values ( SELECT … FROM … )
2. W3Schools - SQL INSERT INTO SELECT Statement
3. DevTip - Insert into … values ( SELECT … FROM … )
4. Tech Champion - Comprehensive Guide on INSERT INTO Using SELECT
5. DB2 Tutorial - Db2 INSERT INTO SELECT By Practical Examples
6. Wikipedia - SQL-92
7. SQLines - MySQL INSERT Guide