MS SQL Server Management Studio export table to csv file - how to

Posted on 2007-10-04
Last Modified: 2010-02-11

We are new to MS SQL Server Management Studio and need to export a table to a csv formatted file. We figure there must be a wizard or some function to do this but have not found it.  Could you give us simple instructions on how to do this.  Thanks.

PS: Searching the knowledge base only gives us solutions outside of MS SQL Server Management Studio. We need solution using MS SQL Server Management Studio if possible.

Question by:RegisCorp
    LVL 16

    Expert Comment

    You can run the bcp command in the command shell with the correct parameters.

    if you type bcp /? you get the parameters
    \bcp /?
    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"]         [-x generate xml format file]

    Or you could run the BCP command in a stored procedure on SQL like this
    EXEC  master..xp_cmdshell @bcpCommand
    When you run the command in SQL you should make sure that your user has enough rights to run the bcp command

    This is an example:
    select @message = 'BCP "SELECT * FROM TABLE" ' +

    print @message
    EXEC  master..xp_cmdshell @message
    LVL 14

    Accepted Solution

    open SSMS --> right click on the database --> select export
    select the data source (you db name) the the tables you need to export and complete the wizard
    LVL 26

    Expert Comment

    BULK INSERT Northwind.dbo.[Order Details]
       FROM 'f:\orders\lineitem.tbl'
             FIELDTERMINATOR = '|',
             ROWTERMINATOR = '|\n'

    LVL 26

    Assisted Solution

    Sorry, that was import. Here:

    with User/Password:
    bcp "SELECT au_fname, au_lname FROM dbname.owner.tablename" queryout C:\Temp\tablename.txt -c -Sservername -Usa -Ppassword

    with trusted connection:
    bcp "SELECT au_fname, au_lname FROM dbname.owner.tablename" queryout C:\Temp\tablename.txt -c  -Sservername -T

    rund this command from command prompt, the files will be created on the local machine, or from Query Analyzer with:
    EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM dbname.owner.tablename" queryout C:\Temp\tablename.txt -c -Sservername -Usa -Ppassword"'

    EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM dbname.owner.tablename" queryout C:\Temp\tablename.txt -c  -Sservername -T'

    in which case the files will be created on the machine where SQL server to which the connection was.

    Expert Comment

    So in all Microsofts' over-developed automations, they can't make a simple import/export function that creates a file to transport from one machine to another machine, allowing what the author of this thread intended. This just blows my mind.

    The question still remains, which is why this should not be closed yet.

    How does one "export" a table with structure and data intact to a file(WITH ALL DATA-TYPES ACCEPTED), then walk to another machine with M$ studio, and upload it with creating custom SQL or shell commands?

    For the love of all that's good! Oy!

    Expert Comment


    There's an easier way.

    In MSSMS, run a select of the whole table.
    eg: SELECT * FROM MyTableName

    Then, right click on the results and click "Save Results As..."


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    794 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

    16 Experts available now in Live!

    Get 1:1 Help Now