TechNet
Export (0) Print
Expand All

Keep Nulls or Use Default Values During Bulk Import (SQL Server)

 

Applies To: SQL Server 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

System_CAPS_ICON_note.jpg Note


For sample format files that skip a table column, see Use a Format File to Skip a Table Column (SQL Server).

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 Create a Format File (SQL Server).

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  
  

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.

CommandQualifierQualifier type
bcp-kSwitch
BULK INSERTKEEPNULLS*Argument

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

System_CAPS_ICON_note.jpg 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:

1Default value of Col2DataField3
2Default value of Col2DataField3

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:

SwitchDescription
-fSpecifies that the command is using a format file..
-kSpecifies that empty columns should retain a null value during the operation, rather than have any default values for the columns inserted.
-TSpecifies 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  
  

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:

CommandQualifierQualifier Type
INSERT ... SELECT * FROM OPENROWSET(BULK...)WITH(KEEPDEFAULTS)Table hint
System_CAPS_ICON_note.jpg Note


for more information, see INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL), and Table Hints (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:

1NULLDataField3
2NULLDataField3

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  
  

To use a format file

To use data formats for bulk import or bulk export

To specify data formats for compatibility when using bcp

BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL)
bcp Utility
BULK INSERT (Transact-SQL)
Table Hints (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft