dumping 100 tables to ascii file
-
Is there anyway to export the data from 100 tables to 100 ascii files.
Without creating a format file for each one or creating a monster DTS package going through each and every table ????
-
How about writing a little code that issues xp_cmdshell "OSQL" commands and uses the -o option to output the data to a file for each of the 100 tables.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
From Query Analyzer you could:
select 'bcp your_db_name..'+name+' out '+name+'.txt -Sserver_name -c -E' from sysobjects where type = 'u'
Grab the results of that query and run them from the command line as a *.bat file. This query assumes that you want all of the user tables in the database.
If it's a one-time thing, you could probably get it done in just a few minutes.
apf
-
Even better - a combo of the 2 suggestions:
Run this in Query Analyzer:
select 'exec master..xp_cmdshell ''bcp your_db_name..'+name+' out '+name+'.txt -Sserver_name -c -E''' from sysobjects where type = 'u'
Cut and paste the results from the results pane back into the query pane and run it. You may want to add a directory path to the output file name.
I just ran this on one of my databases and it worked fine.
apf
-
Even better
exec sp_msforeachtable 'xp_cmdshell ''bcp database.? out ?.dat -S(local) -c -E'''
or something like that
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Simons on trakc but I believe you need to add EXEC before xp_cmdshell or it will assume they are the same piece and may give an error.
exec sp_msforeachtable 'EXEC xp_cmdshell ''bcp database.? out ?.dat -S(local) -c -E'''
-
dumb question:
where is this outputing the files to?
exec sp_msforeachtable 'EXEC xp_cmdshell ''bcp pubs.? out ?.dat -S(local) -c -E'''
I figured out where to specify the location but I have no idea where it was going before:
exec sp_msforeachtable 'use master
EXEC xp_cmdshell ''bcp pubs.? out z:\trash\?.dat -S(local) -c -E'''
Edited by - ksexton on 11/26/2002 06:45:09 AM
-
Opps, it ends up under system or system32 directory depedning on the OS. This shoudl be better.
exec sp_msforeachtable 'EXEC master..xp_cmdshell ''bcp yourdbname.? out yourdrive:\yourpath\?.dat -S(local) -c -E'''
-
how about delegating it to a junior programmer as a learning experience?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply