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.

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.

1 For BULK INSERT, if default values are not available, the table column must be defined to allow null values.

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:

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:

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)

Help and Information

Getting SQL Server 2005 Assistance