SQL Server provides powerful tools for importing and exporting data, such as BULK INSERT and BCP (Bulk Copy Program). These tools allow you to efficiently transfer large amounts of data between text files and SQL Server tables. In this article, we will explore the concepts of BULK INSERT and BCP Format Files and how they can be used to handle various data import scenarios.

The Table

Before we dive into BULK INSERT and BCP Format Files, let’s start by creating a simple test table that we want to import a file into using BULK INSERT. In this example, we will create a table called “SomeTable” with four columns: SomeTableID, ColA, ColB, and ColC.

CREATE TABLE dbo.SomeTable
(
 SomeTableID INT IDENTITY(1,1) NOT NULL,
 ColA INT,
 ColB INT,
 ColC INT
);

The Text File

Now, let’s consider a scenario where the text file we want to import has fewer columns than the table, and the columns are not in the same order. We also want to exclude an extra column from the import. Here is an example of such a text file:

Column_C,Extra,Column_A
1,2,3
4,5,6
7,8,9

In this file, the columns are not in the same order as the table, and there is an extra column that we don’t want to import. The names of the columns are also different. We need to find a way to map the fields in the file to the correct columns in the table.

The BCP Format File

To handle this scenario, we can use a BCP Format File. A BCP Format File is a non-XML file that specifies the format of the data file being imported or exported. It allows us to define the mapping between the fields in the file and the columns in the table.

Here is an example of a BCP Format File that maps the fields in the text file to the correct columns in the table:

8.0
3
1     SQLCHAR   0     8000     ","         4   ColC        ""
2     SQLCHAR   0     8000     ","         0   NotUsed     ""
3     SQLCHAR   0     8000     "\r\n"      2   ColA        ""

In this format file, each line represents a field in the file. The columns in the format file specify the data type, length, delimiter, and the corresponding column in the table. For example, the first line maps the first field in the file (Column_C) to the ColC column in the table.

The BULK INSERT Statement

Now that we have our table and format file ready, we can use the BULK INSERT statement to import the data from the text file into the table. Here is an example of the BULK INSERT statement:

BULK INSERT dbo.SomeTable
   FROM 'C:\Temp\SomeFile.txt'
   WITH (
         BATCHSIZE      = 50000,
        CODEPAGE       = 'RAW',
        DATAFILETYPE   = 'char',
        FIRSTROW       = 2,
        FORMATFILE     = 'C:\Temp\BCP Format File.fmt',
        MAXERRORS      = 2000000000,
        ERRORFILE      = 'C:\Temp\SomeFile.err',
        TABLOCK
        );

In this statement, we specify the table we want to insert into, the file we want to import, and various options for the import process. The BATCHSIZE option controls the number of rows imported as a single transaction. The CODEPAGE and DATAFILETYPE options specify the character encoding and data file type. The FIRSTROW option allows us to skip rows at the beginning of the file, such as headers. The FORMATFILE option specifies the path to the BCP Format File. The MAXERRORS and ERRORFILE options allow us to handle errors during the import process.

Conclusion

In this article, we have explored the concepts of BULK INSERT and BCP Format Files in SQL Server. We have seen how BULK INSERT can be used to efficiently import data from text files into SQL Server tables. We have also learned how to use BCP Format Files to handle scenarios where the file has fewer columns or the columns are not in the same order as the table. By understanding these concepts, you can effectively handle various data import scenarios in SQL Server.

Thank you for reading!