Skip to content
Solution Seeker
Solution Seeker
  • Home
  • Azure
  • SQL
  • Powershell
Solution Seeker

Exporting SQL Query Results from SSMS to CSV

solutionadmin, December 26, 2022September 6, 2023

Sometimes you may have large amount of data you want to export to csv file without having to leave SSMS. For this you can use bcp but you will have to make sure that you have bcp installed.

To check if you have BCP installed simple open command line and type

If bcp is already there then follow steps below. In this example we will be exporting list of databases,database id and create date

Step 1 — Define variables. I like to keep my code as dynamic as possible. This allows me to change values only at few places and avoid any accidents I may cause if I forget to change certain values.

Declare @filename varchar(1000); --This will be main file Declare @tempDatafile varchar(100); --This will be used temp --Change this if your bcp.exe is at a different location Declare @exec_path varchar(200)=' cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ & '; Declare @bcp_cmd4 varchar(1000); Declare @query varchar(8000); Declare @columns varchar(8000);

Setting values for our variables

--FileName with Path Select @filename = 'C:\users\testuser\Desktop\data.csv' Select @tempDataFile = 'C:\users\testuser\Desktop\temp.csv'

@columns variable is being used to add headers to our data file. What I have found is when you export data using bcp it does not export columns names automatically. This is a workaround to get headers added. (If anyone knows another way please do share with me)

--These are the columns we are adding to our output file select @columns=' ''name'',''database_id'',''create_date'' '

Step 2 — Now we are going to create an empty file and add column headers to it.

--Creating File with Columns select @query='bcp.exe " select '+@columns+' " queryout '+@filename+' -c -t, -T -S' + @@SERVERNAME --Combining execution path and our bcp query Set @bcp_cmd4= @exec_path + @query --executing our query exec master..xp_cmdshell @bcp_cmd4

At this point you can run your queries. You should now see an empty file name ‘data.csv’ with headers.

Step 3 — We will now extract our sql data and export that to a separate temporary file.

Set @query='bcp.exe "Select name,database_id,create_date from sys.databases (nolock)" queryout ' + @tempDatafile +' -c -t, -T -S'+@@SERVERNAME Set @bcp_cmd4= @exec_path + @query PRINT @bcp_cmd4; EXEC master..xp_cmdshell @bcp_cmd4;

Once, above query is executed you should see another file named ‘temp.csv’ . If you open file, you will see three columns with database name, id and create date.

Step 4 — Now are going to merge our header and data file.

Note: Make sure that sequence of column name in STEP 1 and STEP 3 is the same. You do not want to have wrong names for columns.

/** Appending temp file at the end of header file **/ set @query= 'type '+@tempDatafile+' >> '+@filename+'' Set @bcp_cmd4= @exec_path + @query exec master..xp_cmdshell @bcp_cmd4

After we have appended temp file to our main file , we can now get rid of our temp file, since it is not needed anymore.

/** Delete temp file **/ set @query= 'del '+@tempDatafile+'' Set @bcp_cmd4= @exec_path + @query exec master..xp_cmdshell @bcp_cmd4
SQL csvdatadatabaseexportsqlssms

Post navigation

Previous post
Next post

Related Posts

Easiest way to move SQL Agent Job to another server

January 9, 2023January 11, 2023

I have learned that it is not always about “doing it yourself” rather it is about – “has anyone already solved the problem” or is there an easier of doing things. In most cases there always is someone who has done it before. While, I was working on automating a…

Read More

Deploying/Copying SSIS Package to another SQL Server

September 6, 2023

There are times you may want to deploy or copy SSIS package from one server to another. Here is a quick and easy way of copying it over using command line.

Read More

Monitoring SQL Servers

January 1, 2023January 1, 2023

I have only been a DBA (database administrator) for a while now. One thing Brent Ozar recommends in his training’s is knowing your inventory. I didn’t realize how important it was until I started patching servers. What I realized it is very easy to get lost in amount of information…

Read More

Leave a Reply Cancel reply

You must be logged in to post a comment.

Recent Posts

  • Deploying/Copying SSIS Package to another SQL Server
  • Easiest way to move SQL Agent Job to another server
  • Monitoring SQL Servers
  • Exporting SQL Query Results from SSMS to CSV
  • Powershell Function to send email
©2023 Solution Seeker | WordPress Theme by SuperbThemes