Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

How do I Export All Tables in a Selected Database to Tab-Delimited Text Files with Field Names Using BCP?

What I want to do is automate the export of all tables in a particular database using BCP.  

The export format must be tab-delimited ASCII text with fieldnames as the first row, and I have to have a separate text file for each table.

I have adapted the following code (hats off to Nigel Rivett), which does everything except insert the field names as the first row:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set nocount on
create table #a (name varchar(128), id int identity)
insert #a (name)  select name from sysobjects where xtype = 'U'
declare @id int, @cmd varchar(2000)
select @id = 0
while @id < (select max(id) from #a)
begin
      select @id = min(id) from #a where id > @id
      select @cmd = 'bcp ' + db_name() + '..' + name + ' out "c:\bcp\' + name + '.txt" -S' + @@servername + ' -c -T'
      from #a where id = @id
      exec master..xp_cmdshell @cmd
      select @cmd
end
drop table #a
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

My question is twofold:

Given my goals, do you see anything wrong with the BCP arguments above?

and

Is it possible to get the field names as the first row too?
0
irvcon
Asked:
irvcon
  • 13
  • 5
2 Solutions
 
MikeWalshCommented:
Quick question.. Do you have to use BCP?

SQL Server DTS is good for you for the following reasons:

1.) It functions in the same way as BCP for what you are trying to do here in the background
2.) It has a great GUI to set this up
3.) It can be customized and configured for easy reusability
4.) It can then be scheduled as a job

For instance in DTS, you just have to create a connection to your source server, then to the destination text file.. You can then use the Data Transformation task to create the file (and specify in the GUI to keep the row headers, specify tab delimit, text qualifiers, etc.).. You can do this for each table and save it and be done.

You can also make it more robust in the future. add error checking, add notification steps, add exception handling rules, etc. etc.

It would take appx 5 minutes to write something like this in DTS.
0
 
irvconAuthor Commented:
I have tried DTS, but could only figure out how to do one table at a time.  There are 804 total.

I am absolutely not married to BCP, but I would like this scripted rather than running through the DTS GUI over and over and over and...
0
 
MikeWalshCommented:
ahhh.. I missed that there were 804 tables.. Well you could do it more automated in DTS, but that would take a little more up front time.. You could do some custom code to iterate through all tables, but in your case maybe BCP would be better after all :)

Although.. If you are just doing this once, you can actually do this through enterprise manager really easily. you can even save the DTS package from it and tweak it later..

but if you right click on your datbase, go to all tasks then export data.. you can then select all tables (just click on each one) as the source, and setup your destination and options..

Pretty simple also.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
irvconAuthor Commented:
> "you can then select all tables (just click on each one) as the source, and setup your destination and options.."

I need separate output tables.  Am I missing how to do that through that GUI?  I only see how to specify one output file.
0
 
MikeWalshCommented:
Nevermind.. that way requires one table at a time, I thought it was different, perhaps that was importing from another DBMS..

0
 
MikeWalshCommented:
so now back to your original question, :-)
0
 
MikeWalshCommented:
It looks like you are on the right track. I don't know of an argument to make the header output occur. Test it and see what default is. It has been a while since I have run a bcp out. Your syntax for the actual BCP looks good. You are using integrated security and you are passing a "dot" for the user so you should be good there.
0
 
MikeWalshCommented:
I found this link talking about the missing ability in bcp to bring over column headers. It is towards the middle of the page..

http://www.sql-server-performance.com/bcp.asp

you could use the same idea to loop through all of your tables and create the view by dynamically creating your create view syntax.. You would then bcp out the views and drop them.. Kind of a pain, but once you coded it up front it would take care of it for you.

You could even do it with temp tables and do it all in one iteration.

1.) Go through the sysobjects for user tables.
 2) for each user table do:
     A) Generate and Execute dynamic SQL to create a temp table with the current tables name that includes the column headers using the process from above page.
    b) Execute the BCP for that table
    c) Drop the table's personal temp table (again dynamic sql)
 3.) Once all tables are looped through then cleanup the temp table that lists all tables.
0
 
MikeWalshCommented:
So it isn't really pretty, but it works. It is probably quicker than doing 804 different data pump tasks, because you only have to write the code up front once.. You get the benefit of using the process for other databases, you get to add your column headings, and you get the format you want..
0
 
MikeWalshCommented:
Jeesh.. that process isn't very pretty at all. You would have to either dynamically create your select list or have your import process for the next stage with these text files ignore the SEQno column.
0
 
MikeWalshCommented:
I am about to go off to bed, but the more I think about this, if you want to have your row headers, then you should really do something with DTS and custom work within it.

I found this:

http://www.sqldts.com/default.aspx?299

this article is entitled "How to export all tables in a database"

It will probably take more than 5 minutes to implement, but I think less time than making something work with bcp, and less time than setting up 804 separate data pumps.. And the fun part is you get to learn something in the process.
0
 
MikeWalshCommented:
So sorry if I confused you more than helped you, but I would either try that link (and you may have to use some of the links from that page depending on your knowledge level of DTS), live without the headers, or mess with dynamic sql and the bcp links. Good Luck and sorry for so many replies, just trying to give you all options.

0
 
xenon_jeCommented:
Based on your code you can create something like this:
(this is good only for tables that have maximum field size less or equal with 500 characters...so no memo field!!)

set nocount on
create table #a (name varchar(128), so_id int, id int identity)
insert #a (name, so_id)  select name, id from sysobjects where xtype = 'U'
declare @id int, @cmd varchar(2000), @so_id int, @name varchar(128)
select @id = 0
while @id < (select max(id) from #a)
begin
     set @cmd = ''
     select @id = min(id) from #a where id > @id


     select @so_id =so_id, @name = name from #a where id = @id      

     select @cmd = @cmd + sc.name + '      ' from syscolumns sc where sc.id = @so_id
     set @cmd = @cmd + ','''
     select @cmd = @cmd + ',''''' from syscolumns sc   where sc.id = @so_id
     set @cmd = '"select ' + @cmd + ' union select '
     select @cmd = @cmd + 'convert(varchar(500), ' + name + '),' from syscolumns sc where sc.id = @so_id
     set @cmd = @cmd + ''''''
     
     select @cmd = 'bcp ' + @cmd + ' from ' + db_name() + '..' + name + '" queryout "c:\bcp\' + name + '.txt" -S' + @@servername + ' -c -T'
     from #a where id = @id

     exec master..xp_cmdshell @cmd
--this is just for testing   select @so_id, @name, @id, @cmd
end
drop table #a

-------
the whole ideea is that instead of bcp table, I used the bcp query!! (query is between " " and not singles ' )
I hope I adjusted it to your tables right.
Of course the query is a union between the field names and a select * (converting to varchar(500) of all fields.
The conversion is because union will try to convert to the same datatype for the unioned fields...which can create problems.

good luck,
  xenon
0
 
MikeWalshCommented:
Points split xenon_je and MikeWalsh
0
 
irvconAuthor Commented:
Neither solution worked, but I appreciate the effort of both.  I will split the points.
0
 
MikeWalshCommented:
well in that case you should have requested the question be closed with points refunded rather than accept the answers with Cs.

We are here to try and help, and we need your input to help you. I apologize that we could not solve your particular issue, but moving forward if something doesn't help you, speak up so we can know we are not on the same page.
0
 
irvconAuthor Commented:
I appreciate both of your efforts *towards* a solution, and felt points were earned even though none were found.  However, considering that the answers did not actually solve the problem, I felt a "C" was the most appropriate choice given that others might be looking to this as reference.

I agree that I needed to be more involved in feedback on this issue, but the truth is that the project went a different direction and I got busy with other priorities.  I personally think the EE system would be improved by sending 7 and 14 day no-activity emails prior to the 21 day abandoned one.  3 weeks is a blip in my life and remembering to update a status here may not get bubbled up to the top without some help.
0
 
MikeWalshCommented:
I agree with you there about the blip and the system.

I definitely was not complaining about the grade, personally I feel like you shouldn't even waste points if you didn't get an answer so that is my major concern.

In fact it seems like this question was just yesterday, I can't believe 21 days had passed!
0
 
irvconAuthor Commented:
I feel you on the 21 day thing.  With respect to points, -you spent time trying to solve the problem.  I appreciate that, and good luck to you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 13
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now