Keeping Nulls or Using Default Values During Bulk Import
By default, when data is imported into a table, the bcp command and BULK INSERT statement observe any defaults that are defined for the columns in the table. For example, if there is a null field in a data file, the default value for the column is loaded instead. The bcp command and BULK INSERT statement both allow you to specify that nulls values be retained.
In contrast, a regular INSERT statement retains the null value instead of inserting a default value. The INSERT ... SELECT * FROM OPENROWSET(BULK...) statement provides the same basic behavior as regular INSERT but additionally supports a table hint for inserting the default values.
Note
For sample format files that skip a table column, see Using a Format File to Skip a Table Column.
Sample Table and Data File
To run the examples in this topic, you need to create a sample table and data file.
Sample Table
The examples require that a table named MyTestDefaultCol2 be created in the AdventureWorks sample database under the dbo schema. To create this table, in Microsoft SQL Server Management Studio Query Editor, execute:
USE AdventureWorks;
GO
CREATE TABLE MyTestDefaultCol2
(Col1 smallint,
Col2 nvarchar(50) DEFAULT 'Default value of Col2',
Col3 nvarchar(50)
);
GO
Notice that the second table column, Col2, has a default value.
Sample Format File
Some of the bulk-import examples use a non-XML format file, MyTestDefaultCol2-f-c.Fmt that corresponds exactly to the MyTestDefaultCol2 table. To create this format file, at the Microsoft Windows command prompt, enter:
bcp AdventureWorks..MyTestDefaultCol2 format nul -c -f C:\MyTestDefaultCol2-f-c.Fmt -t, -r\n -T
For more information about creating format files, see Creating a Format File.
Sample Data File
The example uses a sample data file, MyTestEmptyField2-c.Dat, that contains no values in the second field. The MyTestEmptyField2-c.Dat data file contains the following records.
1,,DataField3
2,,DataField3
Keeping Null Values with bcp or BULK INSERT
The following qualifiers specify that an empty field in the data file retains its null value during the bulk-import operation, rather than inheriting a default value (if any) for the table columns.
| Command | Qualifier | Qualifier type |
|---|---|---|
bcp |
-k |
Switch |
BULK INSERT |
KEEPNULLS1 |
Argument |
1 For BULK INSERT, if default values are not available, the table column must be defined to allow null values.
Note
These qualifiers disable checking of DEFAULT definitions on a table by these bulk-import commands. However, for any concurrent INSERT statements, DEFAULT definitions are expected.
For more information, see bcp Utility and BULK INSERT (Transact-SQL).
Examples
The examples in this section bulk import using bcp or BULK INSERT and keep null values.
The second table column, Col2, has a default value. The corresponding field of the data file contains an empty string. By default, when bcp or BULK INSERT is used to import data from this data file into the MyTestDefaultCol2 table, the default value of Col2 is inserted, producing the following result:
|
|
|
|
|
|
To insert "NULL" instead of "Default value of Col2", you need to use the -k switch or KEEPNULL option, as demonstrated in the following bcp and BULK INSERT examples.
Using bcp and Keeping Null Values
The following example demonstrates how to keep null values in a bcp command. The bcp command contains the following switches:
| Switch | Description |
|---|---|
-f |
Specifies that the command is using a format file.. |
-k |
Specifies that empty columns should retain a null value during the operation, rather than have any default values for the columns inserted. |
-T |
Specifies that the bcp utility connects to SQL Server with a trusted connection. |
At the Windows command prompt, enter.
bcp AdventureWorks..MyTestDefaultCol2 in C:\MyTestEmptyField2-c.Dat -f C:\MyTestDefaultCol2-f-c.Fmt -k -T
Using BULK INSERT and Keeping Null Values
The following example demonstrates how to use the KEEPNULLS option in a BULK INSERT statement. From a query tool, such as SQL Server Management Studio Query Editor, execute:
USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
FROM 'C:\MyTestEmptyField2-c.Dat'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
KEEPNULLS
);
GO
Keeping Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)
By default, any columns that are not specified in the bulk-load operation are set to NULL by INSERT ... SELECT * FROM OPENROWSET(BULK...). However, you can specify that for an empty field in the data file, the corresponding table column uses its default value (if any). To use default values, specify the following table hint:
| Command | Qualifier | Qualifier Type |
|---|---|---|
INSERT ... SELECT * FROM OPENROWSET(BULK...) |
WITH(KEEPDEFAULTS) |
Table hint |
Note
for more information, see INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL), and Table Hint (Transact-SQL)
Examples
The following INSERT ... SELECT * FROM OPENROWSET(BULK...) example bulk imports data and keeps the default values.
To run the examples, you need to create the MyTestDefaultCol2 sample table, the MyTestEmptyField2-c.Dat data file, and use a format file, MyTestDefaultCol2-f-c.Fmt. For information on creating these samples, see "Sample Table and Data File," earlier in this topic.
The second table column, Col2, has a default value. The corresponding field of the data file contains an empty string. When INSERT ... SELECT * FROM OPENROWSET(BULK...) import the fields of this data file into the MyTestDefaultCol2 table, by default, NULL is inserted into Col2 instead of the default value. This default behavior produces the following result:
|
|
|
|
|
|
To insert the default value, "Default value of Col2", instead of "NULL", you need to use KEEPDEFAULTS table hint, as demonstrated in the following example. From a query tool, such as SQL Server Management Studio Query Editor, execute:
USE AdventureWorks;
GO
INSERT INTO MyTestDefaultCol2
WITH (KEEPDEFAULTS)
SELECT *
FROM OPENROWSET(BULK 'C:\MyTestEmptyField2-c.Dat',
FORMATFILE='C:\MyTestDefaultCol2-f-c.Fmt'
) as t1 ;
GO
See Also
Concepts
Keeping Nulls or Using Default Values During Bulk Import
Creating and Modifying DEFAULT Definitions
Other Resources
BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL)
bcp Utility
BULK INSERT (Transact-SQL)
Table Hint (Transact-SQL)