June 2, 2008 at 10:31 am
We have a third party vendor that needs data from us as CSV files. There are several databases that need to be exported, from a SQL SERVER 2000 instance.
I tried using DTS but it only lets me do one table at a time. Is there another tool that will let me export an entire database as CSV? Or at least choose multiple tables?
TIA,
Jim
June 2, 2008 at 10:38 am
Nope, you can perhaps implement some loops to do this. I've used bcp to do this before. Some scripts on this site to help.
Best bet is build the DTS package, use transforms for each table, and then you've got something you can easily repeat. Build it so that you can easily select one transform to run if you need to update one table only.
June 2, 2008 at 11:36 am
Forgive my ignorance here. When you say build the package, do you mean save it as I'm going through the import/export wizard? Could you elaborate a bit there?
June 3, 2008 at 9:03 am
Yes, if you save it, it becomes a DTS package. You can then open the package to add additional events (emails for instance). Simply save from the import/export wizard and within Enterprise Manager, expand DTS and look in local packages. You can do some pretty cool stuff with it.
Edit - As Steve mentioned you can also do this with bcp. Wrap it into a stored proc with the appropriate path names, and you should be good to go. You might be even able to utilize sp_MSForEachTable in the sproc. Just be aware it is an undocumented stored procedure and could be changed by MS at any time. Plenty of hits here and on the web.
http://www.sqlservercentral.com/articles/Advanced+Querying/sp_msforeachtable/181/
http://www.databasejournal.com/features/mssql/article.php/3441031
-- You can't be late until you show up.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply