SQL Transact INSERT ... SELECT Statement
This example not only inserts data, but it inserts records with style.
INSERT... SELECT works well when you already have the data elsewhere, in another
table for example.
Lesson 1 - INSERT ... SELECT
The scenario: You want to add your employees to the customer database so that
you can sell them your products. (Naturally you will give them a staff
discount!) The crucial point is that we have 90% of the information
already typed into the employees database.
Preparation
Let us find out the column (field) names in the customers table
-- Execute a built-in stored procedure to display customers column names
USE Northwind
EXEC sp_help customers
GO
-- Execute a built-in stored procedure to display employees column names
USE Northwind
EXEC sp_help employees
GO
Learning points
Note 1: Stored procedures need to be preceded by EXEC
Note 2: Spot the usefulness of the GO command to separate the two statements.
Result: We have a list of column names for the customers and employees
tables.
Before and after.
To check that the SQL transact statement really is working, let us check the
customer table before and after the INSERT... SELECT.
Make a physical, or mental note of the customers. Less than 100?
-- SQL statement to check the number of customers
-- BEFORE the import
USE Northwind
SELECT customerid, contactname
FROM customers
GO
Problem - CustomerID
Problem: The one troublesome field is the first one, CustomerID.
Troublesome because it does not exist in the employees table and that is where
we are importing the data.
Elegant Solution: Let us derive this CustomerID, as follows,
substring
(firstname, 1, 3) + substring (lastname, 1, 2)
INSERT.... SELECT statement
-- SQL statement to extract employees data
-- then INSERT into customers
USE Northwind
INSERT customers
SELECT substring (firstname, 1, 3)
+ substring (lastname, 1, 2),
lastname, firstname, title, address, city,
region, Postalcode, country, homephone, NULL
FROM employees
GO
Learning Points
Note 1: This creates a field from he first three letters of firstname and
concatenates it to the first two letters of lastname.
Note 2: A surprise is that you do not have to name the first column,
'CustomerID'. The reason is that the name is implicit because this derived
field is the first field and so corresponds to CustomerID.
After the event check
How many customers are there now? Another 9? Total over 100 -
thanks to employee additions.
-- Check the number of customers After the import
USE Northwind
SELECT customerid, contactname
FROM customers
GO
Guy's Out Takes
No Out Takes here, the trouble is that its not easy to delete records and try
again. Them main reason is the cross table constraints that are built in
to the Northwind design which make it almost impossible to delete records.
Tell the truth, I
backup Northwind before I tackle the INSERT statement.
|
