Bulk Insert doubts

Hello Guys,

soon soon, ll have to devellop some procedures to read an ASCII file to supply MS SQL tables. As Ive read some old post, Ive understand that I have to use BULK INSERT , or else, BCP or DTS. Id like to know the diference between this commands and witch of them is more powerful, faster and efficient. If you can give me some implementation tips, I will be very grateful.

thanx allDid you look it up in BOL...

How is the data stored?

anyway...I almost exclusivley use bcp for Production code

BULK INSERT usually for quick data analysis..

and DTS for analysis, if the data is in Excel, Access, whatever...|||DTS is the most flexible, because it can go from more or less automated (using the wizard), to very task oriented (using the painter), to micro-managing the transfer (using VBA).

BCP and BULK INSERT are both tools that expose the current equivalents to the old Bulk Copy API. They are very efficient, but not extremely flexible or friendly.

-PatP|||But what's the most effecient?

EDIT: And do you not think releasing DTS to a production environment is more painful?|||BCP and BULK INSERT are simply differet front ends to the same code. Performancewise the difference is irrelevant.

I live and breathe in a replicated environment. One of our machines does virtually all of our DTS jobs against an "interface" server that has little or no user load.

We use DTS against our OLTP servers, our DW servers, and a number of "friendly" machines that need data. I've never had any real complaints about it.

-PatP|||Well thanx for all replies... Talking about what I have... Ill have 30 ASCII files to load as I told you, and approximatly 91292,42 KB for each... Itll run in production environment, to synchronize the ambient for a BI system... Which comand should I use?

Originally posted by Brett Kaiser
Did you look it up in BOL...

How is the data stored?

anyway...I almost exclusivley use bcp for Production code

BULK INSERT usually for quick data analysis..

and DTS for analysis, if the data is in Excel, Access, whatever...|||I suggest DTS is best and reliable tool to import those ASCII text files to the database. And even you can schedule the same package if its ongoing BI requirement.

As suggested you should follow books online for all the information.
For DTS specifically keep in touch with http://www.sqldts.com.