You can export data from SQL Server using BCP command for SQLCMD utility. However, these utilities does not support dynamic file names when exporting data. For generating dynamic file names you can use solution provided below. In the examples below I have appended date/time to exported files. You can modify the logic to suit your requirement.
Step 1: First let us create a stored procedure which will provide the data to be exported:
CREATE PROCEDURE ExportData
SET NOCOUNT ON
SELECT 'Vishal', 'SqlAndMe'
I have selected string here to keep things simple. You can specify any query in stored procedure which produces required data.
Step 2: Now, we will write the T-SQL code to export data returned from this stored procedure. Here we will use SQLCMD (you can also use BCP) to export data. We will execute SQLCMD using xp_cmdshell extended stored procedure.
DECLARE @sqlCommand VARCHAR(1000)
DECLARE @filePath VARCHAR(100)
DECLARE @fileName VARCHAR(100)
SET @filePath = 'C:\Temp\'
SET @fileName = 'MyFile_' +
+ CONVERT(VARCHAR, GETDATE(), 112) + '_' +
CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +
CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.txt'
SET @sqlCommand =
'SQLCMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" -o "' +
@filePath + @fileName +
–Uncomment if you want to use BCP
–SET @sqlCommand =
– 'bcp "EXEC ExportData" queryout "' +
– @filePath + @fileName +
– ' " -S (local) -T -d SqlAndMe -c'
EXEC master..xp_cmdshell @sqlCommand
The above code will create the required file as "MyFile_YYYYMMDD_HH_MM.txt".
You can verify the command generated by uncommenting the PRINT statement in the code above. Also, you can uncomment the fourth SET statement in case you want to use BCP command to export the data.
Hope This Helps!
Filed under: Management Studio, SQL Agent, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data