When you INSERT into a table which has a column with a default value defined, and you omit that column from your statement, what will happen? The default will be written. What if you write an explicit Null (and the column is nullable)? In nearly all cases the Null is written.
You may not want this – it’s one of the cases where it might be nice if there were more than one type of Null, as some people advocated in the early days. Then we could distinguish a “known unknown” (© D. Rumsfeld) or a “known not to be applicable” (both of these are real information, which presumably should be written) from a missing value (where the default should probably be used). We’ve only got one type of Null, so the best we can do is decide at statement level how the defaults are treated.
I’ve written the sql below to demonstrate that Transact-Sql statements in the local database will always write the Null, even where you use the WITH(KEEPDEFAULTS) table hint. On the other hand, the default behaviour of BCP, BULKINSERT and OPENROWSET when loading a data file is to write the column default for any empty field in the source, unless KEEPNULLS is specified.
If you were wanting this behaviour when your source was a database table, you might think of adapting the FROM clause to use OPENROWSET. Unfortunately it’s only available as a BULK option, so you won’t be able to use the hint. It seems the only option would be to run a second query to update the values as required.
--DROP TABLE SourceTable
CREATE TABLE SourceTable(C1 int NULL, C2 nvarchar(50) NULL, C3 nvarchar(50) NULL)
INSERT INTO SourceTable(C1, C2, C3) VALUES(1, 'Data', 'Data')
--DROP TABLE TargetTable
CREATE TABLE TargetTable(C1 int NULL, C2 nvarchar(50) NULL , C3 nvarchar(50) DEFAULT('Default Value') NULL, Notes nvarchar(100) NOT NULL)
INSERT INTO
TargetTable
(
C1
,Notes
)
VALUES
(
1
,'VALUES clause. C2/C3 omitted.'
)
INSERT INTO
TargetTable
(
C1
,C2
,C3
,Notes
)
VALUES
(
1
,Null
,Null
,'VALUES clause. Explicit Nulls in C2/C3.'
)
INSERT INTO
TargetTable
(
C1
,C2
,C3
,Notes
)
VALUES
(
1
,DEFAULT
,DEFAULT
,'VALUES clause. DEFAULT specified C2/C3 (C2 has no default).'
)
INSERT INTO
TargetTable
(
C1
,Notes
)
SELECT
C1
,'SELECT statement. C2/C3 omitted'
FROM SourceTable
INSERT INTO
TargetTable
(
C1
,C2
,C3
,Notes
)
SELECT
C1
,Null
,Null
,'SELECT statement. Explicit Nulls in C2/C3'
FROM SourceTable
INSERT INTO
TargetTable
(
C1
,C2
,C3
,Notes
)
SELECT
C1
,Null
,Null
,'SELECT statement. Explicit Nulls and KEEPDEFAULTS Hint'
FROM SourceTable WITH(KEEPDEFAULTS)
SELECT * FROM TargetTable
blog.philipsymons.com 