Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

III.7.2. Adding Data to Your Database

The INSERT command is used to add rows to either tables or views.

Some general rules and guidelines to follow when adding data with the INSERT statement are

• NULL data: If a column is specified as NOT NULL, then the INSERT must include data for the column.

• Data type: The data type of the inserted data must match the data type of the column. In other words, if you try to add text data into a numeric column, the INSERT will fail.

• Constraints or rules: If the INSERT violates a constraint or rule on the table, the INSERT will fail. For example, if a constraint specifies that a credit rating column can have values between 1 and 5 only, then an INSERT with a value of 9 will fail.

• Multiple columns: Multiple columns can be added by using a single column list and multiple values lines.

The basic syntax of the INSERT command is

INSERT INTO tableName
       (column list,,,)
VALUES (value,,,),
       (value,,,)

NOTE

In past versions of SQL Server, you had to add a column list for every value line. If you were adding 100 rows to a table with each row populating only some of the columns, you'd have to use 100 column lists. It looked similar to this:

INSERT INTO tableName
       (column list,,,)
VALUES (value,,,),
       (column list,,,)
VALUES (value,,,),
       (column list,,,)
VALUES (value,,,),

In SQL Server 2008, you can use one column list to identify the columns in each of your value lines. It might not seem like much, but if you're forced to repeat the values list 100 times, this change ...