Copying Data From a Data File to SQL Server

Copying Data From a Data File to SQL Server

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

• When bulk copying data to a table with no indexes, set the recovery model to bulk-logged if you usually use full recovery.

  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. For more information, see Logged and Minimally Logged 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 recovery model is simple, then a full or differential backup is recommended; for bulk-logged recovery and full recovery, a log backup is sufficient. For more information, see Backup and Restore 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.

Note  A hidden character in an ASCII data file can cause problems when trying to bulk copy data into an instance of SQL Server, resulting in an "unexpected null found" error message. Many utilities and text editors display hidden characters which can usually be found at the bottom of the data file. Finding and removing these characters should resolve the problem.

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 switches 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
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, you can use the BULK INSERT statement from a query tool, such as SQL 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:

Copying Data Containing Identity Values

The bcp utility and BULK INSERT statement allow data files containing identity values to be bulk copied into an instance of SQL Server. To prevent SQL Server from supplying identity values, the bcp utility accepts the -E switch, and the BULK INSERT statement accepts the KEEPIDENTITY clause. While the rows in the data file are bulk copied into the table, SQL Server does not assign unique identity values automatically; the identity values are taken from the data file.

If these options are not supplied, the values for the identifier column in the data file being imported are ignored and SQL Server assigns unique values automatically based on the seed and increment values specified during table creation. If the data file does not contain values for the identifier column in the table, use a format file to specify that the identifier column in the table should be skipped when importing data. SQL Server assigns unique values automatically for the column.

Importing Image Data

It is possible to bulk copy a data file as image data into an instance of SQL Server. The command to load the data file Test.doc into the bitmap table in the pubs database using the bcp utility is:

bcp pubs..bitmap in test.doc -Usa -Ppassword -Sservername

bcp prompts:

Enter the file storage type of field c1 [image]:
Enter the prefix length of field c1 [4]: 0
Enter length of field c1 [4096]: 5578 
Enter the field terminator [none]:

In this example, the data file will be loaded into column c1, and 5578 is the length of the data file.

Using the BULK INSERT statement, a format file needs to be created first and then used to provide the format information. To create the format file, use the bcp utility:

bcp pubs..bitmap out c:\bitmap.txt -Sservername -Usa -Ppassword

The bcp utility prompts for the file storage type, prefix length, field length, and field terminator of each column of bitmap. The values for the c1 column are listed in this table.

The Bcp.fmt file:

8.0
1
1   SQLIMAGE   0   5578      ""      1   c1

Using the BULK INSERT statement to bulk copy the Test.doc data file into the bitmap table in the pubs database, execute from a query tool, such as SQL Query Analyzer:

BULK INSERT pubs..bitmap FROM 'c:\test.doc'
WITH (
   FORMATFILE = 'c:\Bcp.fmt'
)

Note  You cannot bulk copy data into text, ntext, and image columns that have DEFAULT values.

See Also

bcp Utility

BULK INSERT.

ImportData Method

IncludeIdentityValues Property

SuspendIndexing Property

UseBulkCopyOption Property

Using a Data File with Fewer Fields