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.
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.
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.