INSERT with SQL 2008

April 3, 2009

This article will explore the various methods of using an INSERT statement with SQL Server 2008. After database Tables are created, a method to perform basic data manipulation tasks like inserting, modifying and deleting data is needed. Such data manipulation is accomplished through SQL Server’s own dialect of Structured Query Language (SQL) called Transact SQL or (T-SQL). TSQL Commands can be executed by typing them in directly to SQL Server, or graphically though the SQL Server Management Studio. The SQL Server Management Studio (SSMS) is an easy to use and intuitive graphical tool that lets you build and manage your database graphically. This article will explore the INSERT command of T-SQL’s Data Manipulation Language (DML).

T-SQL Statements can be typed directly into SQL Server by opening a new “Query Window”. To launch a Query Window, open the SQL Server Management Studio, highlight the correct Database, and then click the New Query button on the top menu. A blank Query Window will open.

INSERT Command

Let's start with the most basic syntax of INSERT command.

INSERT [INTO] table_name 
[column_list]
VALUES
default values|values_list

INSERT INTO Customers
	(CustID, CustName)
VALUES
	('Cust1', 'Smith Company');

The INSERT command must be followed by the table name of where you want to add data. The INTO keyword is optional. However, INTO makes the statement more readable. In this example, two columns of information are entered. The order the columns appear in the VALUES line must match the order of the INTO statement. So both the INTO and VALUES lines must match in the number of items and order listed. If the two lines do not match, an error like the following will be thrown:

Msg 110, Level 15, State 1, Line 1
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

All Values Entered

If a value for every column of information is going to be inserted, then it is not necessary to specify the name of each column. But, to use this syntax, every Table column of information must be entered in the same order they are listed in the Table definition.

INSERT INTO Customers
VALUES
	('Cust1', 'Smith Company', '123 West 51 st', 'Tampa', 'FL');

every Table column of information must be entered in the same order they are listed in the Table definition

Multiple Insert Rows

SQL Server 2008 supports the option of inserting multiple records in one statement. Each row of data is followed by a comma until reaching the last row where the INSERT command is completed like normal.

INSERT INTO Customers
	(CustID, CustName)
VALUES
	('Cust1', 'Smith Company'),
	('Cust2', 'Perform Company'),
	('Cust3', 'Test Inc');

Other version of SQL Server require a separate statement to be executed for each record insert.

DEFAULT Columns

If a Column has been defined with a Default value, that value will be inserted into each new row if nothing else is explicitly passed in with the INSERT statement. For example, this table has a default value of ‘WI’ for the State column.

this table has a default value of ‘WI’ for the State column

Executing an INSERT statement like the one shown below, where no value for State is explicitly entered, will default to WI.

INSERT INTO Customers
	(CustID, CustName)
VALUES
	('Cust1', 'Smith Company');

Derived Inserts

INSERT INTO Customers
	(CustID, CustName)
	SELECT CustID, CustName
	FROM LastYearSales

Insert values can be derived from a select statement. In this example, the CustID and CustName were obtained from existing data rather than explicitly typed in. The usual INSERT rules apply such as each column specified in the INTO part of the statement must have a matching Select return.

Other Rules

You cannot insert NULL value or duplicate value to any primary key column.

Foreign key values identify athe relationship between two tables. While inserting or modifying values in the column that holds foreign key constraint, the corresponding values must be present in the related table.

IDENTITY Columns that are auto-filled cannot be inserted with a specific value without first executing a special Insert Identity command; SET IDENTITY_INSERT ON. For example, the following statement demonstrates inserting into an IDENTITY Field. Test is the Table Name.

SET IDENTITY_INSERT test ON

INSERT INTO test
	(myID)
VALUES
	(4)

SET IDENTITY_INSERT test OFF

The value inserted does not need to be the next number the Identity column normally would have used. Meaning if there were three rows previously entered, with IDs of 1, 2, and 3, any number 4 or greater could be used. We are not forced to use 4. We could use 5, 6 or any greater number we wish. Doing so will not cause a problem. The next time auto identity is invoked; it will look for the greatest number then add one.

Management Studio Insert

Now let’s explore how to insert data using SQL Server Management Studio's graphical interface. Right click against the Table in the Object Explorer. Select Edit Top 200 Rows from the context menu.

insert data using SQL Server Management Studio's graphical interface

A data grid with populated data will be displayed as follows. This data grid is very similar to MS Excel and you can enter data in a similar way. The starred row on the left-hand side shows the insertion point to the table. While entering data you must specify acceptable values in accordance with constraints that are set for each column.

While entering data you must specify acceptable values in accordance with constraints that are set for each column

Query Designer

Another graphical way to insert data into a table is using Design View of Query Editor. Right click in a black Query Window and then select Query, Design Query in Editor as shown below.

Right click in a black Query Window and then select Query, Design Query in Editor

From the Add Tables dialog box, select the tables you want to insert or update data. Right click any where in the Query Designer and Select Change Type, Insert Values from the context menu.

From the Add Tables dialog box, select the tables you want to insert or update data

As you enter data in the Column and New Value in the Query Designer’s middle pane, the INSERT INTO script is built. Once you enter the values click OK to exit Design view. Press F5 or Execute button to insert data.

As you enter data in the Column and New Value in the Query Designer's middle pane, the INSERT INTO script is built

To edit your statement after it has been run once, highlight the entire statement, right click anywhere in the highlighted area, and then select Design in Query Editor. If the entire statement is not highlighted, then a new design window will open rather than editing the existing code.

Conclusion

The INSERT Statement enables values to be entered into a SQL Server Table. Statements can be typed in or created graphically with Query Designer. Values can be explicitly type or derived from some other SELECT statement. New in SQL Server 2008 is the ability to enter multiple rows in a single statement.

» See All Articles by Columnist Don Schlichting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers