Welcome to Lesson 4 of the TSQL Tutorial. In this lesson we will cover insert statements. I hope you enjoy the lesson.
Select Statements
INSERT
The INSERT
statement is used to insert rows into a table.
Usage:
INSERT INTO table
VALUES (value1, value2, ...etc)
(or we can also specify the columns for which we want to insert data)
INSERT INTO table (column1, column2, ...etc)
VALUES (value1, value2, ...etc)
Example: We want to insert a record for John Smith into the employee table.
(This is the employees table before we run the insert statement.)
|
EmployeeID
|
LastName
|
FirstName
|
Title
|
BirthDate
|
HireDate
|
Address
|
City
|
|
1
|
Davolio
|
Nancy
|
Sales
Representative
|
12/8/1948
|
5/1/1992
|
507 - 20th
Ave. E.
|
Seattle
|
|
2
|
Fuller
|
Andrew
|
Vice
President, Sales
|
2/19/1952
|
8/14/1992
|
908 W.
Capital Way
|
Tacoma
|
|
3
|
Leverling
|
Janet
|
Sales
Representative
|
8/30/1963
|
4/1/1992
|
722 Moss
Bay Blvd.
|
Kirkland
|
|
4
|
Peacock
|
Margaret
|
Sales
Representative
|
9/19/1937
|
5/3/1993
|
4110 Old
Redmond Rd.
|
Redmond
|
|
5
|
Buchanan
|
Steven
|
Sales
Manager
|
3/4/1955
|
10/17/1993
|
14 Garrett
Hill
|
London
|
|
6
|
Suyama
|
Michael
|
Sales
Representative
|
7/2/1963
|
10/17/1993
|
123 Miner
Rd.
|
London
|
|
8
|
Callahan
|
Laura
|
Inside
Sales Coordinator
|
1/9/1958
|
3/5/1994
|
4726 -
11th Ave. N.E.
|
Seattle
|
|
9
|
Dodsworth
|
Anne
|
Sales
Representative
|
1/27/1966
|
11/15/1994
|
7
Houndstooth Rd.
|
London
|
(Notice that our insert statement below does not specify a value for EmployeeID. This
is due to the fact that the EmployeeID was created as a auto increment field. Our
database automatically uses the next number.)
(Now we will run our insert statement below.)
INSERT INTO employees
VALUES ('Smith', 'John', 'Accountant', '01/01/1970', '02/02/2005', '123 Main St', 'Denver')
Results:
(1 row(s) affected)
(This is the employees table after we run the insert statement. Note that EmployeeID 10
is now John Smith)
|
EmployeeID
|
LastName
|
FirstName
|
Title
|
BirthDate
|
HireDate
|
Address
|
City
|
|
1
|
Davolio
|
Nancy
|
Sales
Representative
|
12/8/1948
|
5/1/1992
|
507 - 20th
Ave. E.
|
Seattle
|
|
2
|
Fuller
|
Andrew
|
Vice
President, Sales
|
2/19/1952
|
8/14/1992
|
908 W.
Capital Way
|
Tacoma
|
|
3
|
Leverling
|
Janet
|
Sales
Representative
|
8/30/1963
|
4/1/1992
|
722 Moss
Bay Blvd.
|
Kirkland
|
|
4
|
Peacock
|
Margaret
|
Sales
Representative
|
9/19/1937
|
5/3/1993
|
4110 Old
Redmond Rd.
|
Redmond
|
|
5
|
Buchanan
|
Steven
|
Sales
Manager
|
3/4/1955
|
10/17/1993
|
14 Garrett
Hill
|
London
|
|
6
|
Suyama
|
Michael
|
Sales
Representative
|
7/2/1963
|
10/17/1993
|
123 Miner
Rd.
|
London
|
|
8
|
Callahan
|
Laura
|
Inside
Sales Coordinator
|
1/9/1958
|
3/5/1994
|
4726 -
11th Ave. N.E.
|
Seattle
|
|
9
|
Dodsworth
|
Anne
|
Sales
Representative
|
1/27/1966
|
11/15/1994
|
7
Houndstooth Rd.
|
London
|
|
10
|
Smith
|
John
|
Accountant
|
1/1/1970
|
2/2/2005
|
123 Main
St
|
Denver
|