How to Insert Data in Text file to SQL Database

Problem:

Insert large amount of data in text file to a SQL database table with minimum time.

 

Solution:

Using ‘Bulk Insert’ is the solution.

I had to import large amount of data in text file to SQL database. If I programmatically insert this data, it would take more time and memory. Therefore, I use SQL bulk insert to import this data. If the fields in the data files are separated using a special character (comma – ‘,’) they can be directly insert in to columns in the database with one query. In addition, it can be fields are not separated, but have known lengths. Then you have to substring them according to lengths.

This describes a situation where all the data fields in the text file are inserted in to same number of columns in the database table. There can be situations where either the database table or the text file may contain additional columns, which are not relevant to the data import. These situations need the support of format file to map data fields in text file to relevant columns in the database. There are some workarounds for some of the special cases but those are additional information to be investigated when required.

Example text file would look like this, 

(ID,FirstName,LastName,Age)

(ID[3 -‘ ‘]FirstName[10 –‘ ‘]LastName[10 – ‘ ‘]Age[2-‘ ‘])

And database table would be like this,

CREATE TABLE [dbo].[TABLE1](

      [ID] [int] NOT NULL,

      [FirstName] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [LastName] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [Age] [int] NULL

)

Simplest Bulk insert query for this table and text file would look like this

BULK INSERT TABLE1

FROM 'D:\test.txt'

WITH (FIELDTERMINATOR =',' , ROWTERMINATOR ='\n')

And the data is in the table simple like that.