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_cmd4At 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_cmd4After 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