CRUD Statements  «Prev  Next»
Lesson 2 Overview of inserting data
Objective Describe how to insert data with Transact-SQL.

Inserting Data using the INSERT statement and Transact SQL

To insert data into a table, you use the INSERT Transact-SQL statement. With the INSERT statement you can specify the names of specific columns with which to insert data, or you can insert data into all of the table columns.
The INSERT statement uses the following general syntax:

Transact-SQL INSERT Syntax

table_name is the name of the table to insert data into
1) table_name is the name of the table to insert data into
INSERT [INTO]
 {
  table_name[WITH(table_hint[...n])]
   | view_name
   | rowset_function
  }

table_hint instructs SQL Server on which index to use if you want to choose a different index from that chosen by the query optimizer. See the graphic below for the table_hint syntax.
2) table_hint instructs SQL Server on which index to use if you want to choose a different index from that chosen by the query optimizer. See the graphic below for the table_hint syntax.

view_name is the name of the view to insert data into
3) view_name is the name of the view to insert data into

rowset_function is the OPENROWSET or OPENQUERY function, if used.
4) rowset_function is the OPENROWSET or OPENQUERY function, if used.

column_list is the list of columns to insert data into
5) column_list is the list of columns to insert data into

Expression is any valid SQL Server expression
6) Expression is any valid SQL Server expression

derived_table is the result of any valid SELECT statement that returns rows of data
7) derived_table is the result of any valid SELECT statement that returns rows of data

execute_statement is any valid EXECUTE statement that returns SELECT data.
8) execute_statement is any valid EXECUTE statement that returns SELECT data. The columns that are returned from the SELECT statement must have datatypes that are compatible with each column in the INSERT statement



The IDENTITY column

Because SQL Server automatically generates the values for the IDENTITY column, it protects these values by not allowing insert statements that contain IDENTITY datatypes. However, if you do wish to insert values into your IDENTITY column, you can instruct SQL Server to allow this by issuing this Transact-SQL statement: 

SET IDENTITY_INSERT table_name ON

Where table_name is the name of the table that contains the IDENTITY column. Remember, only one IDENTITY column can exist in a table. In the next lesson, you will get an overview of the UPDATE statement, which is used to update data in your tables.