Microsoft® SQL Server® 2012 Bible

295

Chapter 12: Modifying Data In SQL Server

12

code, rather than leaving the code blank and assuming the default. The insert-column list

is required when using row constructors to insert multiple rows.

Explicitly listing the columns is a good idea. It prevents an error if the table schema

changes, and it helps document the insert. However, the insert-column list is optional. In

this case, the values are inserted into the table according to the order of the columns in the

table (ignoring an identity column). It’s critical that every table column receive valid data

from the value list. Omitting a column in the value list causes the INSERT operation to fail.

You learned earlier that when the columns are explicitly listed within the INSERT...

VALUES command, an identity column can’t receive a value. Similarly, the identity column

is also ignored in the value list when the columns are assumed. The rest of the values are in

the same order as the columns of the Guide table, as follows:

Use AdventureWorks

go

INSERT INTO dbo.Address

VALUES('3333 Pike Street', 'Seattle', 'WA','Pike', '23674');

To view the inserted data, the following SELECT command pulls data from the Guide

table:

Use AdventureWorks

go

SELECT AddressID, City, State, Address1, County, PostalCode

FROM dbo.Address;

Result:

AddressID City State address1 County PostalCode

---------- ----------- ----- ------------------- ---------------- ----------

1 Houston TX 1411 Mesa Road NULL 77016

2 Baton Rouge LA 444 Perkins Road East Baton Rouge 70808

3 Chicago IL 8765 Buttonwood Walk Cook 60429

5 Seattle WA 3333 Pike Street Pike 23674

However, if you wanted to insert data into the identity column you could use the

SET IDENTITY_INSERT keywords. The syntax is as follows:

Use AdventureWorks

Go

SET IDENTITY_INSERT dbo.Address ON

INSERT INTO dbo.Address(AddressID, Address1, City, State, County,

PostalCode)

VALUES(999,'444 Our Way', 'Detroit', 'MI','Pike', '66666');

SET IDENTITY_INSERT dbo.Address OFF

After running the insert, if you select the data from the table you will see a new row with

the AddressID of 999.

c12.indd 295c12.indd 295 7/30/2012 4:42:38 PM7/30/2012 4:42:38 PM

http://www.it-ebooks.info