SQL Insert Into Statement

The “insert into” statement is used in order to insert data into an existing table. The syntax for this is fairly simple. In the first section of the statement, you specify the table name and column names in which you are inserting data into. The second part is where the source of the data is coming from. There are actually two different ways to specify source data. One way is using a SELECT statement, and the other way using the VALUES statement.

Inserting using the VALUES Statement

The VALUES statement is typically used when either hard coding values to be inserted or when specifying variables to be inserted. The SELECT statement is typically used when querying data from an existing table.

[cc lang=”sql”]
INSERT INTO Customer
(
FirstName
,LastName
)
VALUES(‘Bill’, ‘Jones’)
[/cc]

We see here that the VALUES statement takes the actual column values to be inserted into the Customer table. Using this method, you can only insert one row at a time. With the advent of SQL Server 2008, the VALUES statement can actually insert multiple rows at one time by delimiting with a comma.

[cc lang=”sql”]
INSERT INTO Customer
(
FirstName
,LastName
)
VALUES
(‘Bill’, ‘Jones’)
,(‘John’, ‘Smith’)
[/cc]

INSERT INTO Using the SELECT Statement

The select statement is generally used when selecting from the table.

[cc lang=”sql”]
INSERT INTO Customer
(
FirstName
,LastName
)
SELECT
FirstName = t.First_Name
,LastName = t.Last_Name
FROM Customers_Import t
[/cc]

This statement will actually insert all the customers from the Customers_Import table into the customers table. You will notice in the SELECT statement I set the values from the Customers_Import table equal to the column names used in the Customer table. This will help you to quickly identify that matching column in the target table. It is important to note, column names in the select list need to be in the same order specified in the target table’s INSERT INTO statement.

Column values are inserted based upon matching ordinal positions, not based on the column name that is specified in the SELECT list.

One final good practice note is to always specify column names for the INSERT INTO statement. The following is a bad practice:

[cc lang=”sql”]
INSERT INTO Customer
SELECT *
FROM Customers_Import
[/cc]

I have seen many people do this, only to regret it when a new column gets added to th Customers_Import table that does not match in the Customer table. Always use good practice and specify the column names on every insert.