T-SQL Tutorials - TSQLTutorials.com

INSERT Statement

Introduction

The INSERT statement is used for inserting (adding) data to a table. This SQL Statement begins with the keywords INSERT INTO. There are two methods for inserting data into tables:

Syntax

This first method specifies the columns into which, the corresponding values will be added. The Values and Columns must be in the same corresponding order that you specify them.

INSERT INTO table_name (column1, column2, column3 ..)
VALUES (value1, value2, value3 ..)

In this second method, only the values for the columns are specified. A value for each column in the table must be specified. The Values must be in the same correspoding order in which the columns are in the table.

INSERT INTO table_name
VALUES (value1, value2, value3 ..)

Inserting records by specifying the columns

This example will insert values into the 'Name', 'Position' and 'Salary' columns in the 'Employees' table. Note: All columns in the table do not have to be specified when you are using this type of syntax. In this example we do not insert a value for the 'Office' column.

Table Before INSERT Statement

SELECT *
FROM Employees
Here is a resultset from the above SELECT query:
NamePositionOfficeSalary
Joe GrapeManagerHouston80000
John PlumSoftware DeveloperHouston65000
Frank AppleSoftware DeveloperCleveland62000
Patty PineappleSoftware DeveloperCleveland60000
Judy PeachSoftware DeveloperBoston50000
Jane OrangeProject ManagerHouston75000

INSERT Statement

INSERT INTO Employees (Name, Salary, Position)
VALUES ('Johnny Appleseed','60000','Marketing')

Table After INSERT Statement

SELECT *
FROM Employees
Here is a resultset from the above SELECT query:
NamePositionOfficeSalary
Joe GrapeManagerHouston80000
John PlumSoftware DeveloperHouston65000
Frank AppleSoftware DeveloperCleveland62000
Patty PineappleSoftware DeveloperCleveland60000
Judy PeachSoftware DeveloperBoston50000
Jane OrangeProject ManagerHouston75000
Johnny AppleseedMarketing60000

Inserting a record using all columns in the table

This example will insert a record into the table without specifying the column names. This example will insert values into the 'Name', 'Position', 'Office' and 'Salary' columns in the 'Employees' table.

In this example we will assume that the 'Employees' table contains the follow columns: 'Name', 'Position', 'Office' and 'Salary'.

Table Before INSERT Statement

SELECT *
FROM Employees
Here is a resultset from the above SELECT query:
NamePositionOfficeSalary
Joe GrapeManagerHouston80000
John PlumSoftware DeveloperHouston65000
Frank AppleSoftware DeveloperCleveland62000
Patty PineappleSoftware DeveloperCleveland60000
Judy PeachSoftware DeveloperBoston50000
Jane OrangeProject ManagerHouston75000

INSERT Statement

INSERT INTO Employees
VALUES ('Johnny Appleseed','Marketing','Seattle','60000')

Table After INSERT Statement

SELECT *
FROM Employees
Here is a resultset from the above SELECT query:
NamePositionOfficeSalary
Joe GrapeManagerHouston80000
John PlumSoftware DeveloperHouston65000
Frank AppleSoftware DeveloperCleveland62000
Patty PineappleSoftware DeveloperCleveland60000
Judy PeachSoftware DeveloperBoston50000
Jane OrangeProject ManagerHouston75000
Johnny AppleseedMarketingSeattle60000


Copyright 2010 - TSQLTutorials.com