使用存储过程将表参数结果导出为.txt文件

Question

有没有办法让我将表参数导出到 SQL Server .txt文件中？

我为尝试打印出

DECLARE @testTable TABLE (fld_Name VARCHAR(12), fld_Number VARCHAR(11))
DECLARE @fileTimeStamp varchar(200) =  convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','')  -- select convert(varchar, getdate(), 121)
DECLARE @fileExtension varchar(5) = 'txt'
INSERT INTO @testTable
SELECT [fld_Name] AS fld_Name,
[fld_Number] AS fld_Number
FROM TableBA tblBA
INNER JOIN TableCAS tblCAS ON
tblCAS.fld_Code = tblBA.fld_AccountNumber
WHERE [fld_Name] NOT IN (SELECT [fld_Name] FROM TableLeads)
declare @fn varchar(500) = 'D:/Test/Leads_'+@fileTimeStamp+'.'+@fileExtension;
declare @cmd varchar(8000) = concat('echo ', @testTable, ' > "', @fn, '"');
print @cmd 
exec xp_cmdshell @cmd,  no_output
set @cmd  = concat('type "', @fn, '"');
print @cmd 
exec xp_cmdshell @cmd;

使用它会给我一个错误：

"必须声明标量变量"@testTable">

有没有办法从我创建的表参数打印SELECT *结果？

Answer 1

尝试如下：

declare @tablename varchar(100)='@testTable'
DECLARE @fileTimeStamp varchar(200) =  convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','')  -- select convert(varchar, getdate(), 121)
DECLARE @fileExtension varchar(5) = 'txt'

EXEC ('DECLARE '+@tablename+' TABLE (fld_Name VARCHAR(12), fld_Number VARCHAR(11)) ;
INSERT INTO '+@tablename+'
SELECT [fld_Name] AS fld_Name,
[fld_Number] AS fld_Number
FROM TableBA tblBA
INNER JOIN TableCAS tblCAS ON
tblCAS.fld_Code = tblBA.fld_AccountNumber
WHERE [fld_Name] NOT IN (SELECT [fld_Name] FROM TableLeads);
')
declare @fn varchar(500) = 'D:/Test/Leads_'+@fileTimeStamp+'.'+@fileExtension;
declare @cmd varchar(8000) =  concat('echo ', @tablename, ' > "', @fn, '"');
print @cmd