A little bit of Import/Export

You've been tasked with getting data from a SQL server table into a CSV file. There are many options available, each with their own benefits and drawbacks. In each of the following cases, consider your own task:

• Does it need to be done quickly?
• When should it be done?
• How often?
• Should it be automated?
• How large is the data set?

Good Ol ' Copy and Paste  

If you are running SQL Server Management Server (SSMS), you can run your query results to Grid. This will show the result set as a table on screen. Simply select all and copy (optionally, with headers) and paste directly into a CSV file.  

This is a great method if you need to get a small data set very quickly. 

Right click and Save As   

As previously, you can view your result set in SSMS. Select all and right click. Then click Save As. Again, this is quick, but also gives you more options in choosing file types.  

However, both copy and paste and Save As do not allow for customisation of delimiters.   

Save results to File  

This requires more of a set up in advance. In the Query Options Menu  you can make more changes such as choosing delimiters and  whether or not to include column headers  

You can use Ctrl+Shift+F  to change from seeing the results grid, to saving to file. This is handy if there is no need to see results.  

Data Export Wizard  

This is by far the most thorough method which allows for the most customisation. Select the database you wish to work on. Right click> Tasks > Export Data  

You can choose whether to overwrite an existing file or append data to the end. You can also preview the results and make changes e.g. deleting columns from results or changing data types.  

That said, the export wizard does take a lot more time and is quite hands-on. You may not need this level of customisation.  

Imagine that you need to run a query once all workers have gone home? Surely, there must be a way to schedule a query to run and for the results to be saved? Of course, there is! The following options offer a more automated way of solving our initial problem.  

To start, writing a SQL Job would allow you to decide what to do with your results set. You could email a result set as an attachment using the stored procedure sp_send_dbmail. You could schedule the job to run out of hours. This method requires a knowledge of  Transact-SQL and some setting up.   

Another way of automating this process would be to use the command line with SQLCMD or BCP.  

Bulk Copy Program (BCP)

BCP is designed specifically for copying very large amounts of data from a SQL Server and saving to a file. It is one of the fastest methods and can handle massive amounts of data. 

As you can see to the below, there is a  bit of learning curve when using it. That said, it is a very valuable skill for a database administrator to have.

SQLCMD  

Similar to BCP, SQLCMD can be run in the command line. It can also be run in PowerShell or in a batch file. This would also allow you to use Windows Task Scheduler to choose when the script is run. One advantage over BCP, is that it is easy to pass command line arguments to a SQL file using SQLCMD.