Microsoft SQL Server forum - Default value: ISNULL()

Author

Default value: ISNULL()

Peter Neumaier

2006-01-23, 7:23 am

Hi!

I'm wondering whether it's possible to set up the MS SQL function
ISNULL() as a default value to avoid NULL entries when importing data
into a table?!

For example, I want the column1, to have a 0 (zero) as default value,
when entering/importing data: isnull("column1",0)

I remember that it is possible to set up with a date function like
now(), having for each record the current time as default value. Is
that also with isnull() somehow possible?

Thx a lot!
Peter

Dan Guzman

2006-01-23, 7:23 am

You can create a default constraint to specify a default value for a column.
For example:

CREATE TABLE MyTable
(
Col1 int NOT NULL,
Col2 int NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0
)
GO
INSERT INTO MyTable (Col1) VALUES(1)
SELECT * FROM MyTable
GO

However, an explicit NULL will override the default constraint value:

INSERT INTO MyTable (Col1, Col2) VALUES(2, NULL)
SELECT * FROM MyTable
GO

If you need to import data containing a mix of nulls and not nulls, you have
options depending on your data source and import tool. In the case of a
query, you could use ISNULL or COALESCE to specify the desired value when
NULL. With DTS, a column transformation could do the job.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Peter Neumaier" <Peter.Neumaier@gmail.com> wrote in message
news:1138013760.433105.245790@g43g2000cwa.googlegroups.com...
> Hi!
>
> I'm wondering whether it's possible to set up the MS SQL function
> ISNULL() as a default value to avoid NULL entries when importing data
> into a table?!
>
> For example, I want the column1, to have a 0 (zero) as default value,
> when entering/importing data: isnull("column1",0)
>
> I remember that it is possible to set up with a date function like
> now(), having for each record the current time as default value. Is
> that also with isnull() somehow possible?
>
> Thx a lot!
> Peter
>

Sponsored Links