[SOLVED] Trying to write dynamic sql and export results to a text file

Trying to write dynamic sql and export results to a text file

I'm not sure if this is possible, but I am trying to write some dynamic sql and export the results to a text file. I saw some examples online about exporting the results of a query to a text file, so I tried to copy the concept and combine it with my dynamic sql. The dynamic sql part works, but the part writing to a text file doesn't work. Any thoughts? This is for SQL Server 2008.

Declare @STARTDATE Varchar(30)
Declare @ENDDATE Varchar(30)
DECLARE @SQLQUERY Nvarchar(4000)

SET @STARTDATE = '2017-04-01'
SET @ENDDATE = '2018-04-30'

'bcp' + ' SELECT 'select top 1 '+(select [Source_Column_Name]) ' +
+' from data_mart_us.dbo.' + (select [Source_Table] )  +' where asofdate between '''+ CONVERT(VARCHAR(10),@STARTDATE, 120)+  ''' and ''' + CONVERT(VARCHAR(10),@ENDDATE, 120) + ''''
  FROM [RECONCILIATION].[dbo].DQ_CheckList_ADC
  where database_name like 'data_mart_us' + 'queryout C:\Contacts.txt -c -T''

Solution

This following worked for me:

DECLARE @sqlQuery NVARCHAR(MAX);
DECLARE @filePath NVARCHAR(255);
SET @filePath = 'C:\Path\To\Your\File\output.txt';

SET @sqlQuery = 'SELECT * FROM YourTableName';

-- Create a temporary table to store the results
CREATE TABLE #TempResults (
    Column1 INT,
    Column2 VARCHAR(50),
    -- Add more columns as needed
);

-- Execute the dynamic SQL and insert results into the temporary table
INSERT INTO #TempResults
EXEC sp_executesql @sqlQuery;

EXEC xp_cmdshell '''bcp "SELECT * FROM #TempResults" out "', @filePath, '" -c -T''';

DROP TABLE #TempResults;