While getting ready for Sql Server test 70-761 I was wondering why do I need to insert values from one table in another. That question got answer over the last 3 years while working on different SQL data analysis projects.
This is a common situations that a data analyst or a DBA face day by day when dealing with data-sets.
The syntax for inserting into select is:
INSERT INTO ([TARGET_TABLE](VALUE_1,......,VALUE_N) SELECT [Column_1],..........,[Column_N] FROM [SELECT_TABLE] WHERE [CONDITION]
As you can see the syntax for this is not complicated. The following example will help you to understand much better when do we need to insert filtered values from one table to another.
Contents
1. Demo Data
For this example we are going to create the following tables and insert data into them.
/*DEMO TABLES*/
CREATE TABLE PRODUCT
(
PRODUCT_ID INT NOT NULL PRIMARY KEY
,PRODUCT_DESC VARCHAR(200) NOT NULL
,PRODUCT_WEIGHT_KG DECIMAL(12,2) NOT NULL DEFAULT(0)
);
CREATE TABLE CUSTOMER
(
CUSTOMER_ID INT NOT NULL PRIMARY KEY
,CUSTOMER_DESCRIPTION VARCHAR(200) NOT NULL
,COUNTRY_DESC VARCHAR(200) NOT NULL
,REGION_DESC VARCHAR(256) NOT NULL
);
CREATE TABLE TRANSACTIONS
(
TRX_ID INT NOT NULL
,CUSTOMER_ID INT FOREIGN KEY REFERENCES CUSTOMER(CUSTOMER_ID)
,PRODUCT_ID INT FOREIGN KEY REFERENCES PRODUCT(PRODUCT_ID)
,QUANTITY DECIMAL(12,2) NOT NULL
,SELLING_PRICE DECIMAL(12,2) NOT NULL
,SELLING_DATE DATE NOT NULL
);
INSERT INTO PRODUCT(PRODUCT_ID,PRODUCT_DESC,PRODUCT_WEIGHT_KG)
VALUES('1','Motherboard Asus','0.3')
,('2','Power Supplier','0.2')
,('3','Intercom','0.1')
,('4','Mushkin RAM','0.2')
,('5','ATI Video Card','0.2');
INSERT INTO CUSTOMER(CUSTOMER_ID,CUSTOMER_DESCRIPTION,COUNTRY_DESC,REGION_DESC)
VALUES('1','Alphabet Inc','USA','AMERICA')
,('2','Intel','USA','AMERICA')
,('3','Shazam','UNITED KINGDOM','EUROPE')
,('4','Alibaba','CHINA','ASIA')
,('5','Xiaomi','CHINA','ASIA');
INSERT INTO TRANSACTIONS(TRX_ID,CUSTOMER_ID,PRODUCT_ID,QUANTITY,SELLING_PRICE,SELLING_DATE)
VALUES('1','1','1','40','100.5','20191001')
,('2','1','2','75','20.4','20191002')
,('3','2','3','50','200.3','20191011')
,('4','2','1','30','105.5','20191005')
,('5','5','5','120','90.0','20191025')
,('6','3','2','114','25.0','20191030')
,('7','4','1','70','107.7','20191001')
,('8','5','3','80','190.5','20190501')
,('9','4','5','110','95.0','20190405')
,('10','5','3','150','205.4','20190303')
,('11','5','4','75','40','20190404')
,('12','5','4','80','20','20190504')
,('13','5','4','74','15','20190304');
2. Insert filtered values from one table to another
Imagine that you work for a tech company based in New York and having the following database and scenario at job.
USA is now in a commercial war with China and tech companies based in USA cannot do trades with Chinese companies under the risk of taking government sanctions.
The marketing department told you that you need to create a table to check the impact of this event in your company; they tell you that they need a new table excluding China clients from our current client’s table.
For achieving that, you have to filter your client’s table and then insert the filtered information into another.
/*CHINA CUSTOMERS*/
CREATE TABLE CHINA_CUSTOMERS
(
CUSTOMER_ID INT NOT NULL PRIMARY KEY
,CUSTOMER_DESCRIPTION VARCHAR(200) NOT NULL
,COUNTRY_DESC VARCHAR(200) NOT NULL
,REGION_DESC VARCHAR(256) NOT NULL
);
/*INSERTING SELECT INTO CHINA CUSTOMERS TABLE*/
INSERT INTO CHINA_CUSTOMERS(CUSTOMER_ID
,CUSTOMER_DESCRIPTION
,COUNTRY_DESC
,REGION_DESC)
SELECT CUSTOMER_ID
,CUSTOMER_DESCRIPTION
,COUNTRY_DESC
,REGION_DESC
FROM CUSTOMER
WHERE COUNTRY_DESC NOT LIKE 'CHINA';
As you can see filtered China customers where inserted into the target table as expected.
Now your customer is asking for a table that might return a report on China client’s that were banned, they want to know the quantity of products that we sold to them to see the impact that this decision is going to have in our company.
3. Insert joined values from one table to another
By joining the transactions table with customer table you can obtain a combined table and then filter transactions based on customer country (We are going to use the table that we created on 2. Inserting filtered values from one table to another)
Now we can proceed to insert this selected data in our new table (take into consideration that you can do conversions, case when operations to add some logic before inserting data)
/*Creating China report*/
CREATE TABLE CHINA_REPORT(
CUSTOMER_DESC VARCHAR(250) NOT NULL
,COUNTRY_DESC VARCHAR(250) NOT NULL
,REGION_DESC VARCHAR(250) NOT NULL
,PRODUCT_DESC VARCHAR(250) NOT NULL
,SELLING_MONTH VARCHAR(250) NOT NULL
,REVENUE DECIMAL(16,2) NOT NULL
,CATEGORY VARCHAR(250) NOT NULL
);
/*Inserting from a Join select*/
INSERT INTO
CHINA_REPORT(
CUSTOMER_DESC
,COUNTRY_DESC
,REGION_DESC
,PRODUCT_DESC
,SELLING_MONTH
,REVENUE
,CATEGORY
)
SELECT CC.CUSTOMER_DESCRIPTION
,CC.COUNTRY_DESC
,CC.REGION_DESC
,PI.PRODUCT_DESC
,CONVERT(VARCHAR(6),TA.SELLING_DATE,112) AS SELLING_MONTH
,(QUANTITY * SELLING_PRICE) AS REVENUE
,CASE
WHEN (QUANTITY * SELLING_PRICE) < 10000 THEN 'LOW_REVENUE'
ELSE 'HIGH_REVENUE'
END AS CATEGORY
FROM TRANSACTIONS TA
INNER JOIN CHINA_CUSTOMERS CC ON (TA.CUSTOMER_ID = CC.CUSTOMER_ID)
INNER JOIN PRODUCT PI ON (TA.PRODUCT_ID = PI.PRODUCT_ID);
As you can see in the previous select we added some date conversions, math operations in combination with a case when statement to differentiate high-income vs low-income transactions.
You can select the columns that you want for changing their format to fit your report requirements.
4. Insert into select with a Common table expression
Common table expressions work as subqueries, they are easier to read and cleaner, In this case if we want we can use the query that we created on step 3 and filter the category for obtaining high income customers.
/*CREATING High income china report*/
CREATE TABLE HI_CHINA_REPORT(
CUSTOMER_DESC VARCHAR(250) NOT NULL
,COUNTRY_DESC VARCHAR(250) NOT NULL
,REGION_DESC VARCHAR(250) NOT NULL
,PRODUCT_DESC VARCHAR(250) NOT NULL
,SELLING_MONTH VARCHAR(250) NOT NULL
,REVENUE DECIMAL(16,2) NOT NULL
,CATEGORY VARCHAR(250) NOT NULL
);
/*INSERTING INTO CTE*/
WITH C AS(
SELECT CC.CUSTOMER_DESCRIPTION
,CC.COUNTRY_DESC
,CC.REGION_DESC
,PI.PRODUCT_DESC
,CONVERT(VARCHAR(6),TA.SELLING_DATE,112) AS SELLING_MONTH
,(QUANTITY * SELLING_PRICE) AS REVENUE
,CASE
WHEN (QUANTITY * SELLING_PRICE) < 10000 THEN 'LOW_REVENUE'
ELSE 'HIGH_REVENUE'
END AS CATEGORY
FROM TRANSACTIONS TA
INNER JOIN CHINA_CUSTOMERS CC ON (TA.CUSTOMER_ID = CC.CUSTOMER_ID)
INNER JOIN PRODUCT PI ON (TA.PRODUCT_ID = PI.PRODUCT_ID)
)
INSERT INTO
HI_CHINA_REPORT(
CUSTOMER_DESC
,COUNTRY_DESC
,REGION_DESC
,PRODUCT_DESC
,SELLING_MONTH
,REVENUE
,CATEGORY
)
SELECT CUSTOMER_DESCRIPTION
,COUNTRY_DESC
,REGION_DESC
,PRODUCT_DESC
,SELLING_MONTH
,REVENUE
,CATEGORY
FROM C
WHERE CATEGORY LIKE 'HIGH_REVENUE';
It is inserting data as expected. You can add more data conversions, join, union or other Sql data management sentences to obtain the result that you would like to have.
5. Good practices
This might sound silly and obvious, but It is a good practice to start by creating and testing the select that you want to use in your insert statement. Try to do that before inserting data into your target table to avoid truncate or delete rows that you inserted by accident in your target table.
Do not forget to match on update statement, target table columns with columns that you have on your select statement, this step is necessary to avoid field mismatching.
There are several cases where people omit this step and just apply a Insert statement without establishing target fields (Select *), this can cause to update wrong fields or even provoke an error if you want to use your query that you created months ago and someone change table structure by adding or deleting a field on it.