MSSQLServer – How to insert from select to an existing table

In this tutorial, we’ll use “INSERT INTO SELECT FROM” structure to build an SQL to insert data from a select query to an existing table in MS SQLSERVER (tested with version 2008)

General syntax of “INSERT INTO SELECT FROM”

INSERT INTO TableA SELECT * FROM TableB WHERE col1 = val1;

This general syntax will insert all from SELECT query into TableA. In case TableA does not exist, you have to use another syntax to create a new table from select which is equivalent to CREATE TABLE AS SELECT syntax in Oracle DB. But in MSSQLSERVER, it has a little different:

SELECT INTO TableA * FROM TableB WHERE col1 = val1;

This is so called “SELECT INTO FROM” in MSSQLSERVER syntax ~ “CREATE TABLE AS SELECT” in Oracle.

SQL Optimization

For a better performance, you should make the SELECT query runs fast which is determined by the WHERE condition. So make the WHERE condition smart & right to make the whole query faster.

Related:  convert int to varchar working for SQL Server and MS-Access

For example:

SQL1: INSERT INTO TableA SELECT * FROM TableB WHERE col1 = val1;

SQL2: INSERT INTO TableA SELECT * FROM TableB WHERE CAST(col1,112) = val1;

Basically, in normal condition, CAST command would take resource to complete, so it would be better performance in SQL1 compared to SQL2.

Leave a Reply

Your email address will not be published. Required fields are marked *