Importing/Exporting Data  «Prev  Next»

Lesson 2SQL insert statement
ObjectiveLoad data with the insert statement.

Load data using the 'Insert Statement' in SQL-Server

The simplest way to load data into a database is to use the insert statement. The insert statement can either insert one row or multiple rows into a table. The simplified syntax of the insert statement is shown in the following MouseOver:
The name of the table into which you will insert data
  1. The name of the table into which you will insert data.
  2. A comma-separated list of the columns to insert data into. If this list is not provided, you will insert data into all columns in the table.
  3. Indicates that you are going to provide one row of data.
  4. Uses the default value from the table definition for the associated column.
  5. Sets the associated column to NULL.
  6. Sets the associated column to the expression.
  7. Any select statement that does not use the compute clause.
  8. You can execute any stored procedure that returns data.
  9. Used to specify that you want to use the default for every column in the table.


  1. table_name – The name of the table into which you will insert data.
  2. column_list – A comma-separated list of the columns to insert data into. If this list is not provided, you will insert data into all columns in the table.
  3. VALUES – Indicates that you are going to provide one row of data.
  4. DEFAULT –Uses the default value from the table definition for the associated column.
  5. NULL – Sets the associated column to NULL.
  6. expression – Sets the associated column to the expression.
  7. select_statement – Any select statement that does not use the compute clause.
  8. execute_statement – You can execute any stored procedure that returns data.
  9. DEFAULT VALUES – Used to specify that you want to use the default for every column in the table


Column List Parameter

When you are inserting data into a table, use the column list parameter to specify the columns in the table in which you want to insert data. If you do not specify a specific column from the table, then SQL Server will generate an error and abort the insert, EXCEPT in the following cases:
  1. If the existing columns in the table are identity columns, the next identity value will be inserted into the column.
  2. If the existing columns in the table have a default, the default value will be inserted into the column.
  3. If the existing column’s datatype is timestamp, SQL Server will provide it with the current timestamp value.
  4. If the existing column is nullable, it will get a null value.

You cannot insert a value into derived columns or into identity columns .
  1. Derived columns:A calculated column. SQL Server allows you to define derived columns as part of the table definition.
  2. Identity column :An automatically incrementing column.

Inserting Multiple Rows

By executing a stored procedure or having a select statement as part of the insert statement, you can insert multiple rows of data into an existing table.
The following example illustrates how to insert one row into a table. 

INSERT INTO MyTable ( FirstName, LastName, PhoneNumber)
 VALUES ('John','Doe','111-111-1111')

The following example illustrates how to insert multiple rows into a table. 
INSERT INTO MyTable (FirstName, LastName, PhoneNumber)
 SELECT Fname, Lname, HomePhone
 FROM Employee
INSERT INTO MyTable (FirstName, LastName, PhoneNumber)
 EXECUTE GetPerson @SearchFor = 'A%'

SQL Server provides you the means of creating a table and adding rows to the table in one step. The next lesson will cover using the select into statement to perform this task.