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

yacmzcpb 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(386)

有没有一种方法可以用于将表参数导出到SQLServer中的.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 * 我创建的表参数的结果？

sql sql-server export DataTable export-to-text

来源：https://stackoverflow.com/questions/62709694/exporting-table-parameter-result-as-txt-file-using-stored-procedure

1条答案

按热度按时间

qcuzuvrc1#

试着这样做：

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

赞(0）回复(0）举报 2021-07-29

我来回答

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

1条答案

相关问题

热门标签

最新问答