Sunday, December 9, 2012

How to Insert Data in Text file to SQL Database

Insert large amount of data in text file to a SQL database table with minimum time.
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[3 -‘ ‘]FirstName[10 –‘ ‘]LastName[10 – ‘ ‘]Age[2-‘ ‘])

And database table would be like this,

      [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

FROM 'D:\test.txt'

And the data is in the table simple like that.


  1. Hi I have a question for you...
    what if the data in text file like this below


    now I want an output like

    Cola Colb
    AA BB
    CC DD

  2. Workaround:
    You can run a macro on the text file to edit it to a usable format using a text editor (npp). Easy thing just record and run. Say if want help with that.

  3. Thank you for such an interesting article. There are lots of information about the latest technology, but this one is unique according to me.
    secure data room

  4. Cloud computing has proven a boon to businesses—especially small businesses, for which it hits a particularly sweet spot. With cloud services, small businesses reap the benefits of not having to deploy physical infrastructure like file and e-mail servers, storage systems or shrink-wrapped software.
    online data room services