Copying Data from a Data File to SQL Server

To bulk copy a data file to Microsoft® SQL Server™, follow these guidelines:

• When bulk copying data to a table with no indexes, set the select into/bulkcopy database option to true.

  This is recommended to help prevent the transaction log from running out of space because row inserts are not logged. The system administrator or database owner can set this option with SQL Server Enterprise Manager (or the sp_dboption system stored procedure). For more information, see Logged and Nonlogged Bulk Copy Operations.

• If you are loading a large amount of data relative to the amount of data already in the table, it can be quicker to drop the indexes on the table before performing the bulk copy operation.

  Conversely, if you are loading a small amount of data relative to the amount of data already in the table, dropping the indexes may not be necessary because the time taken to rebuild the indexes can be longer than performing the bulk copy operation. For more information, see Optimizing Bulk Copy Performance.

• Be sure that the user account used to log in to SQL Server using bcp (or the query tool when using the BULK INSERT statement) has SELECT and INSERT permissions on the table (assigned by the table owner).

  ---

  Note Only members of the sysadmin fixed server role can execute the BULK INSERT statement.

  ---

• If the bulk copy operation was not logged, (select into/bulkcopy was set to true), back up the database by creating either a full or differential database backup after bulk copying data from a data file. This ensures that the bulk copy and any subsequent changes can be recovered in the event of a system failure. For more information, see Backing Up Nonlogged Operations.

To bulk copy data successfully into a table from a data file with the bcp utility or BULK INSERT statement, the terminators in the data file must be known, and specified.

The Newpubs.dat file:

1111,Stone Age Books,Boston,MA,USA

2222,Harley & Davidson,Washington,DC,USA

3333,Infodata Algosystems,Berkeley,CA,USA

Because the data file is all character data, the following options and parameters need to be specified.

In the Newpubs.dat file, each field in a row ends with a comma (,); each row ends with a newline character (\n).

The publishers2 table in the following example can be created by executing:

USE pubs

GO

sp_dboption pubs, 'select into/bulkcopy', 'true'

GO

SELECT * INTO publishers2 FROM publishers

GO

The command to bulk copy data from Newpubs.dat into publishers2 is:

bcp pubs..publishers2 in newpubs.dat -c -t , -r \n -Sservername -Usa -Ppassword

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat'
WITH (

    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'

)

Data from the Newpubs.dat file has been now appended to publishers2:

See Also