Keep nulls or default values during bulk import (SQL Server)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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.

Outline

Keeping Null Values Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...) Example Test Conditions  ● Sample Table  ● Sample Data File  ● Sample Non-XML Format File Keep Nulls or Use Default Values During Bulk Import  ● Using bcp and Keeping Null Values without a Format File  ● Using bcp and Keeping Null Values with a Non-XML Format File  ● Using bcp and Using Default Values without a Format File  ● Using bcp and Using Default Values with a Non-XML Format File  ● Using BULK INSERT and Keeping Null Values without a Format File  ● Using BULK INSERT and Keeping Null Values with a Non-XML Format File  ● Using BULK INSERT and Using Default Values without a Format File  ● Using BULK INSERT and Using Default Values with a Non-XML Format File  ● Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File  ● Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Keeping Null Values

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. For OPENROWSET, by default, any columns that are not specified in the bulk-load operation are set to NULL.

Command	Qualifier	Qualifier type
bcp	-k	Switch
BULK INSERT	KEEPNULLS*	Argument
INSERT ... SELECT * FROM OPENROWSET(BULK...)	N/A	N/A

* 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.

Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)

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, use the table hint KEEPDEFAULTS.

Note

For more information, see INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL), and Table Hints (Transact-SQL)

Example Test Conditions

The examples in this topic are based on the table, data file, and format file defined below.

Sample Table

The script below creates a test database and a table named myNulls. Notice that the fourth table column, Kids, has a default value. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNulls ( 
   PersonID smallint not null,
   FirstName varchar(25),
   LastName varchar(30),
   Kids varchar(13) DEFAULT 'Default Value',
   BirthDate date
   );

Sample Data File

Using Notepad, create an empty file D:\BCP\myNulls.bcp and insert the data below. Note that there is no value in the third record, fourth column.

1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,1963-11-14
3,Stella,Rosenhain,,1992-03-02

Alternatively, you can execute the following PowerShell script to create and populate the data file:

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'MyNulls.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

Sample Non-XML Format File

SQL Server support two types of format file: non-XML format and XML format. The non-XML format is the original format that is supported by earlier versions of SQL Server. Please review Non-XML Format Files (SQL Server) for detailed information. The following command will use the bcp utility to generate a non-xml format file, myNulls.fmt, based on the schema of myNulls. To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. The format option also requires the -f option. In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following command:

bcp TestDatabase.dbo.myNulls format nul -c -f D:\BCP\myNulls.fmt -t, -T

REM Review file
Notepad D:\BCP\myNulls.fmt

Important

Ensure your non-XML format file ends with a carriage return\line feed. Otherwise you will likely receive the following error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

For more information about creating format files, see Create a Format File (SQL Server).

Keep Nulls or Use Default Values During Bulk Import

The examples below use the database, datafile, and format files created above.

Using bcp and Keeping Null Values without a Format File

-k switch. At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Using bcp and Keeping Null Values with a Non-XML Format File

-k and -f switches. At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Using bcp and Using Default Values without a Format File

At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Using bcp and Using Default Values with a Non-XML Format File

-f switch. At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Using BULK INSERT and Keeping Null Values without a Format File

KEEPNULLS argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
	FROM 'D:\BCP\myNulls.bcp'
	WITH (
		DATAFILETYPE = 'char',  
		FIELDTERMINATOR = ',',  
		KEEPNULLS
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Using BULK INSERT and Keeping Null Values with a Non-XML Format File

KEEPNULLS and the FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myNulls.fmt',
		KEEPNULLS
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Using BULK INSERT and Using Default Values without a Format File

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
	  );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Using BULK INSERT and Using Default Values with a Non-XML Format File

FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myNulls.fmt'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File

FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myNulls.bcp', 
		FORMATFILE = 'D:\BCP\myNulls.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

KEEPDEFAULTS table hint and FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
WITH (KEEPDEFAULTS) 
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myNulls.bcp', 
		FORMATFILE = 'D:\BCP\myNulls.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Related Tasks

• Keep Identity Values When Bulk Importing Data (SQL Server)

• Prepare Data for Bulk Export or Import (SQL Server)

To use a format file

• Create a Format File (SQL Server)

• Use a Format File to Bulk Import Data (SQL Server)

• Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

• Use a Format File to Skip a Data Field (SQL Server)

• Use a Format File to Skip a Table Column (SQL Server)

To use data formats for bulk import or bulk export

• Import Native and Character Format Data from Earlier Versions of SQL Server

• Use Character Format to Import or Export Data (SQL Server)

• Use Native Format to Import or Export Data (SQL Server)

• Use Unicode Character Format to Import or Export Data (SQL Server)

• Use Unicode Native Format to Import or Export Data (SQL Server)

To specify data formats for compatibility when using bcp

• Specify Field and Row Terminators (SQL Server)

• Specify Prefix Length in Data Files by Using bcp (SQL Server)

• Specify File Storage Type by Using bcp (SQL Server)

See Also

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