SQL Server Insert Into Select Statement

by

In SQL Server, you can use the Insert into select statement to insert data into a table from the SELECT Query. This is useful when copying data from one table to another.

Let’s learn more about insert statements in SQL Server Management Studio.

The syntax for Insert into Select Statement

Below is the syntax for inserting into a select statement in SQL Server.

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

Copy Data from One table to Other

Now, we will see how to copy data from one table to another. You should have two tables with proper columns for this.

CREATE TABLE CUSTOMERS (
    customerID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Address NVARCHAR(50),
   CITY NVARCHAR(50),
   Country NVARCHAR(50),
   );

CREATE TABLE Suppliers (
    SupplierID INT PRIMARY KEY,
   Name NVARCHAR(50),
    Address NVARCHAR(50),
     City NVARCHAR(50),
	 country NVARCHAR(50),
);

Insert into Customers ( customerID, Name, Address, City,country)
            values (1, 'Robert', 'Obere Street','Berlin', 'Germany'),
			       (2, 'David', 'Mataderos', 'México', 'Poland')
			
Insert into Suppliers ( SupplierID, Name, Address, City,country)
            values (11, 'Thomson', 'Church street','Amsterdam', 'Netherland'),
			       (12, 'William', 'Coronot green', 'Toronto', 'canada')

Now, the Supplier’s details are copied into the customer table.

Insert into Select Statement

Note – ID will not be NULL; if the column is mismatched, it will throw an error

When you try to insert fewer values, you can see the error below. The select values must match the INSERT values.

SQL insert into select statement

Insert Top 1 statement into Other Table

Now, use the below syntax to copy the top 1 value into another table. Here, I am trying to copy the top 1 value of the supplier table to the Customer table.

INSERT TOP(1) INTO CUSTOMER
(customerID, 
 Name
)
       SELECT SupplierID,Name 
       FROM Supplier;

select * from CUSTOMER
Insert into Select Statement in SQL Server

Since we have copied the top1 top-value, the rest of the column shows null in the table.

Map Column between Source and Destination

We can map the column between source and destination. Use the below code to do so.

INSERT INTO CUSTOMER
(customerID, 
 Name
)
       SELECT SupplierID,Name 
       FROM Supplier;
Insert into Select in SQL Server

Insert into Temporary table

Use the syntax below to copy one table value into a temporary table.

	   SELECT SupplierID, Name, Address, City, country
	INTO #tbl_Restaurant_Addresses
	FROM Supplier
	

Select * from #tbl_Restaurant_Addresses
Insert into Temporary table

Conclusion

This tutorial explained how to insert into select statements by different scenarios. Try from your end and see the changes in your table.

You may like to read the following tutorials: