devxlogo

How to read data file into SQL tables

How to read data file into SQL tables

Question:
I’m curious to know, if I have a file full of data separated by commas, how do I read stuff from that file into SQL tables?

Answer:
The fastest way to get it into tables is to use BCP (MSSQL 6.5), (Or if you have SQL 7, the DTS).

BCP will let you specify separators and delimiters as well as other characteristics about the job, but it is a command line interface that isn’t fun. I have found that it is best to import your data into SQLCHAR columns in staging tables. Once you have succesfully imported the data into the server, you can use the built in functions to massage your data into its correct datatypes and into its proper target table.

DTS on the other hand, is much more fun, if a little slower. It has many more options to condition the data during import/export.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist