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.
Let us find out the column (field) names in the customers table
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?
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,
INSERT…. SELECT statement
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.
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, Ibackup Northwind before I tackle the INSERT statement.