Inserting into Tables with Identity Columns

For this example, I want to go back to the inventory table Ipresented in the identity section earlier today. When you insertinto a table with an identity column, do not include the identitycolumn in your column list. Because you are leaving out one of thecolumns in the table, you must provide a column list in the INSERT:

insert   inventory(item_id,   qty)   values(45,   5)
insert   inventory(qty,   item_id)   values(3,   46)
go
select  *   from inventory

(1   row(s)   affected)
(1   row(s)   affected)
inv_id      item_id        qty
------      --------       ------
1           45             5
2           46             3
(2   row(s)   affected)

To insert rows to an identity column, do not specify itin the INSERT statement. The first INSERTprovides values for item_id first, and qty second. The order inwhich the data values are listed is dictated by the order of thecolumns in the column list following the table name.

The identity column received a 1 for the first row thatwas inserted and a 2 for the second row. The serverprovided a unique value for each row that was higher than the lastrow inserted in the table.

Inserting into Tables with Default Columns

To allow a row to accept the default value for the column,simply omit it from the INSERT list. This example omitsvalues for the qty column in the inventory table and then selectsthe contents to show that the default of 1 was inserted:

insert inventory(item_id) values(704)
select * from   inventory

(1    row(s)   affected)
inv_id       item_id       qty
------       ---------     -------
  1           45           5
  2           46           3
  3           704          1
  3     row(s)   affected)

The two rows from the identity example are still there.When I added this row, an identity value was provided by the serverand then the default for quantity was inserted.

If you want to give qty a null result, you must providenull explicitly to override the default:

insert    inventory(item_id,   qty)   values(71,   null)
select   *   from    inventory

(1 row(s) affected)
inv_id       item_id       qty
-------      --------      ------
 1           45            5
 2           46            3
 3           704           1
 4           71            (null)
(4 row(s) affected)

Using Niladic Functions as a Default

Microsoft has defined five niladic functions for use withdefault constraints. Three of them all do the same thing, so thereare really just three functions you can use. The niladic functionsare listed in Table 9.1.

Here is a table that makes use of niladic functions:

create table sales(
    sal_id int identity(1,1),
    insert_time datetime not null default CURRENT_TIMESTAMP,
    username varchar(30) not null default USER,
    uid smallint not null default user_id(USER),
    amt smallmoney null
    )
go
insert sales(amt) values ($35)
insert sales(amt) values($62)
go
select * from sales
go
(1 row(s) affected)
(1 row(s) affected)
sal_id     insert_time            username       uid      amt
---------- ---------------------- -------------- -------- ----------
1           Dec 22 1996  8:37 PM  bmcewan        5         35.00
2           Dec 22 1996  8:37 PM  bmcewan        5         62.00

(1     row(s)   affected)
(1     row(s)   affected)
sal_id          insert_time           username   uid      amt
----------      ------------          -------    -----   ----
 1              Dec 22 1996  8:37 PM  bmcewan    5       35.00
 2              Dec 22 1996  8:37 PM  bmcewan    5       62.00