Export data in SQL 2005 using Bulk Copy Program

Posted on 2008-11-12
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.  

Question by:thewayne73
    LVL 5

    Expert Comment


    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?



    Author Comment

    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?


    Author Comment

    I did find this:

    master..xp_cmdshell 'bcp "select statment"'

    but not sure how to use it.
    LVL 5

    Expert Comment


    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

    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!

    Author Comment

    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'

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

    Accepted Solution



    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'


    Author Comment

    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.

    LVL 5

    Expert Comment

    Hi thewayne73,


    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.


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Value of 0's not appearing. 9 41
    Stored procedure 4 21
    What does this SQL mean? 7 33
    Delete from table 6 26
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now