SQL Transact - INSERT ... SELECT

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, Ibackup Northwind before I tackle the INSERT statement.

SQL 2000 Transact – INSERT … SELECT Statement