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.

devx-admin

Share the Post: