import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server.md

title	Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server) | Microsoft Docs
ms.custom
ms.date	06/14/2017
ms.prod	sql-server-2014
ms.reviewer
ms.suite
ms.technology	dbe-bulk-import-export
ms.tgt_pltfrm
ms.topic	article
helpviewer_keywords	BULK INSERT statement, importing data from a remote data file bulk importing [SQL Server], methods bulk exporting [SQL Server], methods OPENROWSET function, BULK INSERT bulk importing [SQL Server], security bulk rowset providers [SQL Server] bulk exporting [SQL Server], BULK INSERT statement remote data access [SQL Server], bulk importing bulk importing [SQL Server], BULK INSERT statement Transact-SQL bulk export/import operations
ms.assetid	18a64236-0285-46ea-8929-6ee9bcc020b9
caps.latest.revision	41
author	JennieHubbard
ms.author	jhubbard
manager	jhubbard

Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server)

This topic provides an overview of how to use the [!INCLUDEtsql] BULK INSERT statement and the INSERT...SELECT * FROM OPENROWSET(BULK...) statement to bulk import data from a data file into a [!INCLUDEssNoVersion] table. This topic also describes security considerations for using BULK INSERT and OPENROWSET(BULK…), and using these methods to bulk import from a remote data source.

Note

When you use BULK INSERT or OPENROWSET(BULK…), it is important to understand how [!INCLUDEssNoVersion] version handles impersonation. For more information, see "Security Considerations," later in this topic.

BULK INSERT Statement

BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the [!INCLUDEssNoVersion] process. For a description of the BULK INSERT syntax, see BULK INSERT (Transact-SQL).

Examples

For BULK INSERT examples, see:

• BULK INSERT (Transact-SQL)

• Examples of Bulk Import and Export of XML Documents (SQL Server)

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

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

• Specify Field and Row Terminators (SQL Server)

• Use a Format File to Bulk Import Data (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)

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

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

OPENROWSET(BULK…) Function

The OPENROWSET bulk rowset provider is accessed by calling the OPENROWSET function and specifying the BULK option. The OPENROWSET(BULK…) function allows you to access remote data by connecting to a remote data source, such as a data file, through an OLE DB provider.

To bulk import data, call OPENROWSET(BULK…) from a SELECT…FROM clause within an INSERT statement. The basic syntax for bulk importing data is:

INSERT ... SELECT * FROM OPENROWSET(BULK...)

When used in an INSERT statement, OPENROWSET(BULK...) supports table hints. In addition to the regular table hints, such as TABLOCK, the BULK clause can accept the following specialized table hints: IGNORE_CONSTRAINTS (ignores only the CHECK constraints), IGNORE_TRIGGERS, KEEPDEFAULTS, and KEEPIDENTITY. For more information, see Table Hints (Transact-SQL).

For information about additional uses of the BULK option, see OPENROWSET (Transact-SQL).

Examples

For examples of INSERT...SELECT * FROM OPENROWSET(BULK...) statements, see the following topics:

• Examples of Bulk Import and Export of XML Documents (SQL Server)

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

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

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

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

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

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

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

Security Considerations

If a user uses a [!INCLUDEssNoVersion] login, the security profile of the [!INCLUDEssNoVersion] process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. In contrast, if a [!INCLUDEssNoVersion] user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the [!INCLUDEssNoVersion] process.

For example, consider a user who logged in to an instance of [!INCLUDEssNoVersion] by using Windows Authentication. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a [!INCLUDEssNoVersion] table, the user account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the [!INCLUDEssNoVersion] process does not have permission to access the file. The user does not have to grant file-access permission to the [!INCLUDEssNoVersion] process.

[!INCLUDEssNoVersion] and [!INCLUDEmsCoName] Windows can be configured to enable an instance of [!INCLUDEssNoVersion] to connect to another instance of [!INCLUDEssNoVersion] by forwarding the credentials of an authenticated Windows user. This arrangement is known as impersonation or delegation. Understanding how [!INCLUDEssNoVersion] version handle security for user impersonation is important when you use BULK INSERT or OPENROWSET. User impersonation allows the data file to reside on a different computer than either the [!INCLUDEssNoVersion] process or the user. For example, if a user on Computer_A has access to a data file on Computer_B, and the delegation of credentials has been set appropriately, the user can connect to an instance of [!INCLUDEssNoVersion] that is running on Computer_C, access the data file on Computer_B, and bulk import data from that file into a table on Computer_C.

Bulk Importing from a Remote Data File

To use BULK INSERT or INSERT...SELECT * FROM OPENROWSET(BULK...) to bulk import data from another computer, the data file must be shared between the two computers. To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form, **\\Servername\Sharename\Path\**Filename. Additionally, the account used to access the data file must have the permissions that are required for reading the file on the remote disk.

For example, the following BULK INSERT statement bulk imports data into the SalesOrderDetail table of the AdventureWorks database from a data file that is named newdata.txt. This data file resides in a shared folder named \dailyorders on a network share directory named salesforce on a system named computer2.

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail  
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';  
GO  

Note

This restriction does not apply to the bcp utility because the client reads the file independently of [!INCLUDEssNoVersion].

See Also

INSERT (Transact-SQL)
SELECT Clause (Transact-SQL)
Bulk Import and Export of Data (SQL Server)
OPENROWSET (Transact-SQL)
SELECT (Transact-SQL)
FROM (Transact-SQL)
bcp Utility
BULK INSERT (Transact-SQL)