Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...)

This topic provides an overview of how to use the Transact-SQL BULK INSERT statement and the INSERT...SELECT * FROM OPENROWSET(BULK...) statement to bulk import data from a data file into a Microsoft SQL Server 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 SQL Server 2005 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 SQL Server 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 Importing and Exporting XML Documents
• Copying Data Between Different Collations
• Keeping Identity Values When Bulk Importing Data
• Keeping Nulls or Using Default Values During Bulk Import
• Specifying Field and Row Terminators
• Using a Format File to Bulk Import Data
• Using Character Format to Import or Export Data
• Using Native Format to Import or Export Data
• Using Unicode Character Format to Import or Export Data
• Using Unicode Native Format to Import or Export Data
• Using a Format File to Skip a Table Column
• Using a Format File to Map Table Columns to Data-File Fields

OPENROWSET(BULK…) Function

SQL Server 2005 introduces the OPENROWSET bulk rowset provider, which 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 Hint (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 Importing and Exporting XML Documents
• Keeping Identity Values When Bulk Importing Data
• Keeping Nulls or Using Default Values During Bulk Import
• Using a Format File to Bulk Import Data
• Using Character Format to Import or Export Data
• Using a Format File to Skip a Table Column
• Using a Format File to Skip a Data Field
• Using a Format File to Map Table Columns to Data-File Fields

Security Considerations

If a user uses a SQL Server login, the security profile of the SQL Server process account is used.

In contrast, if a SQL Server 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 SQL Server process.

For example, consider a user who logged in to an instance of SQL Server 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 SQL Server 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 SQL Server process does not have permission to access the file. The user does not have to grant file-access permission to the SQL Server process.

SQL Server and Microsoft Windows can be configured to enable an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as impersonation or delegation. Understanding how SQL Server 2005 handles 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 SQL Server 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 SQL Server 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. For more information, see Impersonation Overview.

Note

The way in which SQL Server 2005 controls access to files addresses a security issue that was present in Microsoft SQL Server 2000 and earlier versions. Formerly, after a user was authenticated, access to external files was based on the security profile of the SQL Server process. When the SQL Server process had read access to a file, for a user that did not have access to the file but was a member of the bulkadmin fixed server role, the user could import the file by using BULK INSERT and access the contents of the file.

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. The user account that is used by SQL Server must have been granted 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 AdventureWorks.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 SQL Server.

See Also

Concepts

Importing and Exporting Bulk Data
About Bulk Import and Bulk Export Operations
Basic Guidelines for Bulk Importing Data

Other Resources

INSERT (Transact-SQL)
Impersonation Overview
INSERT (Transact-SQL)
SELECT Clause (Transact-SQL)
OPENROWSET (Transact-SQL)
SELECT (Transact-SQL)
FROM (Transact-SQL)
bcp Utility
BULK INSERT (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	Changed content: Moved content about the bcp utility to the new topic Importing and Exporting Bulk Data by Using the bcp Utility. Revised the "OPENROWSET(BULK…) Function" section. Inserted the sections "Security Considerations" and "Bulk Importing from a Remote Data File." Added "Additional Examples" links to conceptual topics that contain examples.
5 December 2005	New content: Added discussion of how to use a Transact-SQL command to import data from a remote data file.