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.
|Bulk copy option||bcp utility switch||BULK INSERT clause|
|Character mode format||-c||DATAFILETYPE = 'char'|
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:
|0736||New Moon Books||Boston||MA||USA|
|0877||Binnet & Hardley||Washington||DC||USA|
|1111||Stone Age Books||Boston||MA||USA|
|1622||Five Lakes Publishing||Chicago||IL||USA|
|2222||Harley & Davidson||Washington||DC||USA|
|9952||Scootney Books||New York||NY||USA|
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
Enter the file storage type of field c1 [image]: Enter the prefix length of field c1 : 0 Enter length of field c1 : 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.
|File storage type||Image|
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.