Data Manipulation Language
Read this chapter and take notes on the purpose and use of the clause SELECT and how the INSERT and DELETE statements are used to make changes to data. Also, note how functions are used in these statements.
INSERT statement
Inserting rows with a SELECT statement
We can sometimes create a small temporary table from a large table. For this, we can insert rows with a SELECT statement. When using this command, there is no validation for uniqueness. Consequently, there may be many rows with the same pub_id in the example below.
This example creates a smaller temporary Publishers table using the CREATE TABLE statement. Then the INSERT with a SELECT statement is used to add records to this temporary Publishers table from the publis table.
CREATE TABLE dbo.tmpPublishers (
PubID char (4) NOT NULL ,
PubName varchar (40) NULL ,
city varchar (20) NULL ,
province char (2) NULL ,
country varchar (30) NULL DEFAULT ('Canada')
)
INSERT tmpPublishers
SELECT * FROM Publishers
In this example, we're copying a subset of data.
INSERT tmpPublishers (pub_id, pub_name)
SELECT PubID, PubName
FROM Publishers
In this example, the publishers' data are copied to the tmpPublishers table and the country column is set to Canada.
INSERT tmpPublishers (PubID, PubName, city, province, country)
SELECT PubID, PubName, city, province, 'Canada'
FROM Publishers