SQL Server Tutorialprovides basic and advanced concepts of C# for beginners and professionals.

  1. Home
  2. SQL Server Tutorial

SQL Insert Into Select Statement

Back to: SQL Server Tutorial

The INSERT INTO ... SELECT statement in SQL is used to insert data into a table from another table or query result. It allows you to copy data between tables or insert data based on a query result. Here’s the basic syntax and some examples to help you understand how it works.

Basic Syntax

INSERT INTO target_table (column1, column2, column3, ...) SELECT value1, value2, value3, ... FROM source_table WHERE condition;

Explanation

  • target_table: The table into which you want to insert the data.
  • (column1, column2, column3, ...): The columns in the target table where you want to insert the data.
  • source_table: The table from which you are selecting the data.
  • value1, value2, value3, ...: The values to be inserted into the target table, typically coming from the SELECT statement.
  • WHERE condition: A condition to filter which rows are selected from the source table.

Example 1: Basic Insert from One Table to Another

Suppose you have two tables: employees and new_employees. You want to copy all employees from the employees table to the new_employees table.

INSERT INTO new_employees (employee_id, first_name, last_name, hire_date) SELECT employee_id, first_name, last_name, hire_date FROM employees;

Example 2: Insert with a Condition

Suppose you only want to insert employees who were hired after January 1, 2024.

INSERT INTO new_employees (employee_id, first_name, last_name, hire_date) SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE hire_date > '2024-01-01';

Example 3: Insert with Calculated Values

You can also insert data with calculated or static values. For example, inserting new employees with a default department.

INSERT INTO new_employees (employee_id, first_name, last_name, hire_date, department) SELECT employee_id, first_name, last_name, hire_date, 'Sales' FROM employees WHERE hire_date > '2024-01-01';

Example 4: Insert into a Table from a Join

You can insert data from a join between multiple tables. For instance, if you want to insert data into target_table from a join of table1 and table2.

INSERT INTO target_table (column1, column2, column3) SELECT t1.column1, t2.column2, t1.column3 FROM table1 t1 JOIN table2 t2 ON t1.common_column = t2.common_column;

Things to Consider

  • Data Types: Ensure that the data types of the columns in the SELECT statement match those of the columns in the INSERT INTO table.
  • Constraints: Be aware of any constraints (like primary keys or unique constraints) on the target table that might affect your insert operation.
  • Transactions: If inserting a large amount of data, consider using transactions to ensure data integrity and performance.
Scroll to Top