Table dump to flat file

[Mich]

I've searched and searched and cannot find a solution to this problem. I simply cannot believe it is that difficult, so I'm going to put out here for you guys.

I have a database with 400 tables. I need to dump all of the data from each table into seperate delimited, text qualified flat files. Doing this for one table is easy, but how do I iterate through all of my tables?

SSIS allows me to iterate, but it fails because the tables have different structures.

I can use SSIS to call BCP and iterate, but I cannot add a text qualifier through BCP.

I can use SSMS's Export Data wizard, but that only allows one table at a time.

I'm out of ideas here. Surely this isn't as difficult as I'm making it.

Thanks.

-If it ain't broke, break it and make it better.

 

[aalmeida]

you can use something like this:

sp_MSforeachtable @command1='bcp "SELECT * FROM ?" queryout "FileName.csv" -T -c'

If u need to do this for all DB's use something like this:
sp_msforeachdb @command1="exec sp_MSforeachtable @command1 = 'bcp "SELECT * FROM " queryout "FileName.csv" -T -c'"


AL Almeida
CIO

http://www.artdigitalonline.com/

May all those that come behind us, find us faithful

 

[SQLWilts]

Doing this will take a loooong time. What are you trying to achieve, or rather why are you trying to do this?
Excuse my ignorance, but what do you mean by "text qualified"?
Assuming SQL 2005, you know how to use dynamic SQL and bcp.
Create a cursor for all table names in a database

select name from sysobjects where xtype = 'U'

Then, populate a variable with the bcp statement to export it to text.
This is untested, so play:

declare @sql nvarchar(4000)
declare @tablename nvarchar(256)


declare tablecursor cursor for

        select name from sysobjects where xtype = 'U'

	OPEN tablecursor

	fetch next from tablecursor into @tablename

	while @@FETCH_STATUS = 0

begin

set @sql = 'bcp ' + @tablename + ' out ' + @tablename + '.csv /U /P /S[your_SQL_Server_Name_here] /c /t,'

select @sql
--EXEC sp_executesql @sql

	FETCH NEXT FROM tablecursor INTO @tablename

end

			close tablecursor
			deallocate tablecursor

As it stands, it will only produce the SQL. Comment out the "select @sql" line and uncomment the EXEC line.

 

[Mich]

I can use SSIS and BCP to iterate through and create my flat files. Basically, I execute a statement to grab all table names in my db (select name from sys.tables), use a ForEach loop to loop through each table, and call a BCP statement on each loop to generate the flat file. This works fine, but doesn't give me the text qualified data.

By "text qualified" I mean every string element placed in the flat file is surrounded by double quotes. While this is supported in BCP through format files, this isn't useful to me because each table's structure is different thus requiring me to have a different format file for each BCP. This means no automated iteration. Damn, that was a mouthful.

There are several 3rd party apps that do this, but I don't want to pay hundreds of dollars for something that SHOULD be pretty simple to do, but I guess 'simple' is relative.

-If it ain't broke, break it and make it better.

 

[SQLWilts]

I see what you mean.

You may be into writing something quite complicated here.

Putting double quotes around text fields makes importing it (for example back into SQL server) a complete nightmare.

I take it that you want to export your data into another DBMS?

You may be able to use this:

http://blog.attobase.com/?page_id=5

and iterate through your tables using SSIS?