Output data to text files
-
Hello all
I hope that your day is going well.
The table names in the table are actually tables in SQL that are storing data. This data I need to export into Text files. But I cannot sit and do a single export on each of the 450 tables by hand. I want to write a DTS package that can loop thru the table names int he table and then select out and create the text file for each and every one. At the same time, give me an indication when one query would file and let me know what the table name is.
Using a DTS package, I am wanting to loop thru a selection of table names, these I am storing in another table, there are approx 450 table names. I want to be able to write my DTS package to go get a table name, and use that as a variable in the select top 500 * from <table_name> and then in the Text file Destination use that same <table_name> variable in the path statement. That way I can easily tell what the text file contains. And then I need this to loop thru the entire table of table names.
How can I write a DTS package to perform this task?
Thank you in advance for all your advice
Andrew
-
Though this isn't tested code you could do something like
DECLARE @dbName varchar(256) SELECT @dbName = Min(Table_Name) FROM TableHoldingTableNames WHILE @dbName IS NOT NULL BEGIN DECLARE @exportSQL varchar(8000) SELECT @exportSQL = 'bcp "SELECT TOP 500 * from ' + @dbName + '" queryout ' + @dbName + '.txt -E' EXEC master..xp_cmdshell @exportSQL -- select the next database in the list SELECT @dbName = Min(Table_Name) FROM TableHoldingTableNames WHERE Table_Name > @dbName END
Julian Kuiters
juliankuiters.id.au -
what about using DTS wizard ?.. right click on your Database and All Tasks>> export Data
and in the last screen Save your DTS package .. so you can use it again
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply