Override default values in INSERTs

from the Artful SQL Server & Access Tips List

Odds are that at least some of your tables have columns with default values. Common defaults include GetDate(), which returns the current system date and time, and User_Name(), which returns the login of the current user. You can also default money columns to zero. Doing so simplifies calculations by abolishing NULL values, but it's controversial: some database designers insist it is a mistake to model an unknown value as zero. Suppose you create a table with columns that all have default values. For example:
CREATE TABLE [dbo].[TestDefaults2] (
  [PK] [int] IDENTITY (1, 1) NOT NULL ,
  [DateEntered] [datetime] NOT NULL,
  [SomeText] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [EnteredBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

ALTER TABLE [dbo].[TestDefaults2] ADD
  CONSTRAINT [DF_TestDefaults2_DateEntered] DEFAULT (getdate()) FOR
  CONSTRAINT [DF_TestDefaults2_SomeText] DEFAULT ('This is some text') FOR
  CONSTRAINT [DF_TestDefaults2_EnteredBy] DEFAULT (user_name()) FOR
    )  ON [PRIMARY]
Paste this code into Query Analyzer and run it against a test database, then run Enterprise Manager, open your test database, expand the Tables node, and open the table. Or if you prefer, you can use Access. Create an ADP file that talks to the test database and then open the table. When you try to insert a row, you'll see that you can't. It seems that SQL Server won't let you insert a row into a table whose every column has a default. Actually that's not the case. You can't insert a row interactively, but you can insert a row using this special syntax:
There are several reasons why you may want to insert rows into a completely defaulted table. For example, this might be an audit-trail table with extra columns in which the update, insert, or delete occurs; in which case, this code might be a trigger rather than a stored procedure. Add columns to suit your requirements, then default them. Or write a stored procedure to add undefaulted values using parameters.

Last updated 22 May 2009

Return to the Artful SQL Server & Access Tips page