Use the INTO statement to extract Information from one table and place it in another
SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table. It is possible to write the INSERT INTO statement in two forms.
The first form does not specify the column names where the data will be inserted, only their values:
INSERT INTO table_name VALUES
(value1,value2,value3,...);
The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
Assume we wish to insert a new row in the "Customers" table. We can use the following SQL statement:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
The CustomerID column is automatically updated with a unique number for each record in the table when you use the INSERT INTO statement.
It is also possible to only insert data in specific columns. The following SQL statement will insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns (and the CustomerID field will of course also be updated automatically):
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
When you work with information in a database, sometimes it can be helpful to create a copy of the information that fits your criteria in a spot that can hold on to it for some time. In addition, there are times when you want simply to copy or move information from one database table to another.
In these instances, there is a simple but very effective modification to the SELECT statement that you can consider using: the INTO clause. The really nice thing about the SELECT...INTO statement is that it will automatically create the table for you and will make it match the information that you have selected out of the original data source. Here's the syntax of the SELECT...INTO statement:
SELECT what INTO destination
FROM source
If you want to create a table that contains just the last name from the customer table, you could use the following statement:
SELECT lastname INTO TempTable
FROM Customer
The result is a table with a single column, the Lastname column, populated with the values from the customer table.
This is helpful if you want to create a table based on the results of a query of a separate table.
Unified Star Schema
TempTable consisting of lastname from the Customer table
One thing to keep in mind about SELECT...INTO is that it creates the table that is the target.
You cannot use this statement if your destination table
already exists.
