SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Export a SSMS Query Result Set to CSV

Once you have a query that is returning the data you like in SSMS you have a few options to move the result set into a format that you can use in other tools.

The simplest way is to select all results and then copy & paste them into whichever other tool you are using. This is great for smaller sets of data, and works very well when using tools such as MS Excel.

There are some cases though where the quantity of data returned does not fit into the excel spreadsheet, or you may want to be able to import data into a different tool. To do this you need to export the data from the query set to a format that can be referenced by the other tools.

There are 2 options that can be done. The first is to execute the query with the results being output to a file. The second is to configure a data export based on a query.

 

Execute query results to a file

  1. Build your Query

    Build your Query

    Open SSMS (SQL Server Management Service) and open / create the query for the data you are looking for.

  2. Next we have to configure the output to file options.


  3. On the menu bar select: Tools then Options

    Select Tools then Options

    Select Tools then Options

  4. On the left select Query Results, SQL Server, Results to Text.

    Set the Results options

    Set the Results options

  5. Set output format to CSV. You can also mark the Display Results in a separate tab and the switch to results tab after the query executes if you like. Click OK to save the changes.

  6. Now when you go to export your query results you have a few options to enable to results to file feature. Press Ctrl+Shift+F to set the query to file destination (Ctrl+D will revert back to the grid output). You can also select Query, Results To & Results to File. There is also a quick button located on the SSMS Standard Toolbar.

    Set the Results to go to Text

    Set the Results to go to Text

    Or use the button on the toolbar

    Or use the button on the toolbar

  7. After having selected the output format, you can Execute the query. You will be prompted to enter a file name and location for the output file. The file will be created with a .rpt extension, but it is a just a plain text file.

    Set the File Name

    Set the File Name

  8. After the export you can browse to the directory and change the extension of the output file to .CSV if you like, and then open the file to verify the contents.

 

 

Export query results to a file


  1. Open SSMS (SQL Server management Studio) and open / create the query for the data you are looking for.

    Write your Query

    Write your Query

  2. Select all of your query text and Copy it to the clipboard (Ctrl+C).

  3. In the Object explorer, right click on the database you wish to export data from and select Tasks, Export Data

    Select Tasks then Export Data

    Select Tasks then Export Data

  4. The SQL Server Import and Export Wizard will open with the data source defaulted to the database you right-clicked on. Leave this as it is and click Next.

    Choose your Data Source

    Choose your Data Source

  5. Select Flat File Destination for the destination and provide the file name and other options. Once done, click Next to continue.

    Set the Destination

    Set the Destination

    image020Set the File Name

    Set the File Name

  6. On the next screen you are asked if you want to Specify Table Copy or Query. Select the Write a query to specify the data to transfer radio button and then click Next.

    Get ready to put in your own query!

    Get ready to put in your own query!

  7. Paste the Query

    Paste the Query

    Paste your query code from step #2 into the SQL Statement box. Click Parse to ensure syntax is correct. Once it is all good, click OK to close dialog box and then click Next to continue.

    Valid SQL is good!

    Valid SQL is good!

  8. You can click Edit mappings if you like to map data types and transformations during the export for more accurate data. When ready click Next.

    Change mappings if you need to

    Change mappings if you need to

  9. Leave Run immediately marked and click Next

    Yes! Run it now!

    Yes! Run it now!

  10.  

  11. Review actions to be performed. When ready click Finish

    All Done!

    All Done!

  12. You will see process complete indicating success (if there are errors you will need to determine why, it may just be a lack of permissions to save files on the server, etc.). You will also see a count of records that were transferred. Click Close when done.

  13. Now you can use the new file in whichever tool you prefer.

Read the full article here

The post Export a SSMS Query Result Set to CSV appeared first on .

WaterOx SQL

Chris Bell (MCITP) has been working with Microsoft technologies for over 20 years and using SQL Server for over 18 of those years. He is currently the Owner and Lead consultant for WaterOx Consulting, a Maryland based company focused on helping clients, that can’t afford or justify a full time data team, get the most from their SQL Server environments. For more information please visit WateroxConsulting.com.

Comments

Leave a comment on the original post [wateroxconsulting.com, opens in a new window]

Loading comments...