Export data in SQL 2005 using Bulk Copy Program

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.

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

I did find this:

master..xp_cmdshell 'bcp "select statment"'

but not sure how to use it.

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!

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).

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.

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.