Data Management

Insert default columns into your tables with SQL

Has SQL Server ever "stopped" you from inserting a row into a table whose every column has a default? If so, check out this tip to learn how to insert rows into a completely defaulted table.

Odds are that at least some of your tables contain columns whose values are defaulted. 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 default money columns to zero. This is controversial because, on one hand, doing so simplifies various calculations since you don't need to worry about NULL values; on the other hand, some database designers despise this kind of thing and insist that NULL and zero are distinctly different values.

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. (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. However, that's not the case. You can't insert a row interactively, but you can insert a row using the following 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, and then default them or alternatively write a stored procedure to add the undefaulted values using parameters.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox