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

x
?
Solved

Export data in SQL 2005 using Bulk Copy Program

Posted on 2008-11-12
8
Medium Priority
?
1,572 Views
Last Modified: 2012-05-05
Hello Experts,

I have select statement and would like to export the data into a .txt file using Bulk Copy.  

Thanks....
0
Comment
Question by:thewayne73
  • 4
  • 4
8 Comments
 
LVL 5

Expert Comment

by:jose_juan
ID: 22940462
Hi,

uhm... what's problem? you put "Bulk Copy" on "SQL Query Syntax" zone but.

SQL Syntax for bulk only is

   BULK INSERT ...

and only import data into sql tables.

To export data you would use bcp (Bulk Copy) but this is not a sql syntax command.

what do you need do?

Regards.




0
 

Author Comment

by:thewayne73
ID: 22940504
Hi Jose,

I am trying to do and export of the file.  Basically, I have a simple select from a view that returns around 70,000 records.  I would like to save that output to a .txt programatically.  

Is there a way to save the output in the sql statement itself?

thanks
0
 

Author Comment

by:thewayne73
ID: 22940687
I did find this:

master..xp_cmdshell 'bcp "select statment"'

but not sure how to use it.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 5

Expert Comment

by:jose_juan
ID: 22940908
Hi,

the only way (I known) to export txt data from sql instruction are mapping a text datasource (e.g. linked server) then use a trivial insert

INSERT INTO my_linked_txt..my_table ( ... )
SELECT ...
FROM my_huge_table

but I suggest to you use a bcp.exe command line

bcp.exe "my_database.my_user.my_table" out my_output.txt -S my_sql_server ....

see options executing

>bcp.exe
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]

over sql instructions you can execute this command using

exec master..xp_cmdshell 'bcp.exe ...'

but your user must have sysadmins role.

Good luck!
0
 

Author Comment

by:thewayne73
ID: 22941000
Thanks jose,

here is the script I have:

EXEC master..xp_cmdshell 'bcp "
select *
  from database..table_view
 order by 3"
queryout "\\servername\folder\output file.txt" -Sservername'
GO

but when i run it, i just get the output that you listed above (usage).
0
 
LVL 5

Accepted Solution

by:
jose_juan earned 1500 total points
ID: 22941188
Hi,

uhm....

You must effort...

DON'T get my listed above, the first line (I'm sure) is a message alert (warn to you must use the -U or -T parameters).

on the other hand, read "bcp.exe" documentation to get your EXACT solution and undertand.

come on!

NOTE: try it

exec master..xp_cmdshell 'bcp.exe "my_db..my_table" out "c:\data.txt" -T -c'
exec master..xp_cmdshell 'dir c:\*.txt'
exec master..xp_cmdshell 'type c:\data.txt'




0
 

Author Comment

by:thewayne73
ID: 22942940
There were a couple of problems.  

- The script needs to be on a single line when running from query
- '-S' I was not using the servername it was running from, instead was using server name the file is being saved to.


0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22947794
Hi thewayne73,

problems?

You can write the script with many lines, but it (the xp_cmdshell parameter) MUST BE a command line (cmd.exe), you should refer to "cmd.exe" documentation for a complete support.

Without -S parameter, the script run perfectly if you run locally (obviously).

No matter, but I disagree of your B grade. Your question:

"I have select statement and would like to export the data into a .txt file using Bulk Copy. "

It had answer fully.

Regards.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question