Data Insertion from Flat file into SQL through BCP Utility

Introduction

I would like to explain the BCP operation to perform the data insertion from Flat file into SQL Table. Very recently, I had this experience from one of the requirements with the kind of issue on bulk insertion process from the client data in CSV format into the database table. Immediately, I come up with this article. Some of you may be faced with similar situations, it may be required to perform the Data insertion into Table from the Input feed file in which the feed file can be CSV format, XLS format, TXT format or any flat format with the data. I will come up with step by step procedures.

Background

In order to perform SQL Table data insertion from any Flat file through BCP command utility, you should have:

• BCP Utility in your system (in order to confirm if BCP is available in your system, just open the CMD and type "BCP /?". Immediately, you should be prompted with the list of options for the given BCP command.
• SQL Server 2005/2008/2012 with Admin access privileges (i.e., Just for data insertion into any table, should have write privileges on DB level)
• CSV or any Flat file (Flat file can be txt, or any format)
• It is speed, effective and efficient way of inserting the data into Table without SSMS interference irrespective of the feed file which could be any format or any style.

Using the Code

Step 1: CMD with Admin Privilege

Just open Command file with 'Administrator' privilege (i.e. Open as Administrator).

Step 2: To Ensure Availability of BCP Utility

Type BCP /? to ensure the BCP utility is available in your system and your system is ready to perform the BCP operation.

Step 3: Table Creation

Create the simple table in your database:

CREATE TABLE TEST
(NAMEFIRST VARCHAR(50),
NAMELAST VARCHAR(50),
PHONE VARCHAR(50),
DISABLED NUMBER)
)

Step 4: Creation of Format File

The format file is identity and it has properties of table data that helps to identify the appropriate field between input feed file and destination table.

For example, In "Test" table, we know Disabled is the 4^th Column represented as Number, but while performing the BCP operation, the "Disabled" column values have been available from CSV file should match.

Please note, if the format file is not created or referred, then it will prompt the user to provide the datatype, size during the BCP insert process.

BCP Command to create the fmt file:

>Bcp tiso.dbo.TEST format nul -c -t, -f D:\tiso\tiso_FORMAT.fmt 
-S SW01 -Utisouser -Puser -e d:\tiso\err.txt

The format file will be created like below through running the above command automatically.

Step 5: Data Insertion from CSV into SQL Server Table

Now, it is time to perform the data insertion. The feed file (here it is CSV format) should be comma separated file that contains the list of fields to insert into destination table:

BCP Command to perform the Data insertion from CSV into SQL Table:

>Bcp tiso.dbo.TEST in D:\tiso\tisobcp_test.CSV 
-f d:\tiso\tisobcp_FORMAT.fmt  -S SW01 -Utisouser -Puser -e d:\tiso\err.txt

CSV Input Feed File

Step 6: Verification / Validation on SQL Server

Now the data is successfully uploaded into Table.

Merits on Performing the Data Insertion Through BCP Utility

• It is pretty simple and an effective way of data insertion.
• The feed file can be any format or any style in which input format file reference will drive the data insertion into table effectively.
• The error can be traced in Err.txt file that helps end user to identify and rectify the error in case of any discrepancies.
• The above BCP operation is performed through SQL Authentication as provided with -S -U and -P which stands for Server name, User ID and password respectively. If we need to perform through Windows authentication, just provide -S and -T stands for Server and Trusted Mode.

Cheers!!