INSERT with SQL 2008

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

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Latest Articles