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

insrf1ej 于 12个月前发布在其他

关注(0)|答案(2)|浏览(135)

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''

sql-server

来源：https://stackoverflow.com/questions/50397853/trying-to-write-dynamic-sql-and-export-results-to-a-text-file

2条答案

按热度按时间

vwoqyblh1#

It will help you but no method uses pure t-sql.

8 Ways to Export SQL Results To a Text File : http://www.sqlservercentral.com/articles/Export/147145/

赞(0）回复(0）举报 12个月前

ktca8awb2#

This Worked for me, Hope this help:

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 (
    -- Define columns based on your query result structure
    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" queryout "' + @filePath + '" -c -T';

DROP TABLE #TempResults;

赞(0）回复(0）举报 12个月前

我来回答

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

2条答案

相关问题

热门标签

最新问答