Insert into Select | Insert into Select SQL Server - sql - sql tutorial - learn sql



 design form of select statement insertion
  • The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
  • INSERT INTO SELECT requires that data types in source and target tables match.
  • The existing records in the target table are unaffected.

INSERT INTO SELECT Syntax

  • Copy all columns from one table to another table:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

Demo Database

  • In this tutorial we will use the well-known Northwind sample database
  • Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados
y helados
Ana Trujillo Avda. de la
Constitución 2222
México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
  • And a selection from the "Suppliers" table:
SupplierID SupplierName ContactName Address City PostalCode Country
1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona EC1 4SD UK
2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 MNew Orleans 70117 USA
3 Grandma Kelly's Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA

Read Also

Insert into sql.

SQL INSERT INTO SELECT Examples

  • The following SQL statement copies "Suppliers" into "Customers" (the columns that are not filled with data, will contain NULL):

Example:

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
  • The following SQL statement copies "Suppliers" into "Customers" (fill all columns):

Example:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;
  • The following SQL statement copies only the German suppliers into "Customers":

Example:

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';


Related Searches to insert into select sql server - SQL tutorial