Sql Server通过BCP数据导出Excel
1.1. bcp的主要参数介绍
-- 这里是创建一个临时表
SELECT *
INTO TempCOrder
FROM ( SELECT '账号' a , '签约银行' b ,'子账号' c , '类型' d , '金额' e , '出入时间' f --这里是因为导出的时候Excel表没有列,这里采用union all为表增加一个列标题
UNION ALL
SELECT u.userName ,
CASE c.ConBankType
WHEN '2' THEN '农业银行'
WHEN '1' THEN '华夏银行'
END back ,
c.subuser ,
CASE f.reason
WHEN '3' THEN '库存结算'
WHEN '4' THEN '银行入金'
WHEN '5' THEN '银行出金'
WHEN '6' THEN '手工调账'
END reason ,
CONVERT(VARCHAR(24), f.changevalue) ,
CONVERT(VARCHAR(24), f.opertime, 121)
FROM dbo.Fund_Change f
INNER JOIN dbo.Trade_User u ON f.userId = u.userId
INNER JOIN dbo.Trade_FundInfo c ON f.userId = c.userId
WHERE u.[state] = '1'
AND c.[state] = '2'
AND f.opertime BETWEEN @BeginDate
AND @EndDate
+ @AgentId
AND f.reason IN ( '3', '4', '5', '6' )
UNION ALL --数据查询
SELECT '总计:' ,
'' ,
'' ,
'' ,
CONVERT(VARCHAR(24), SUM(f.changevalue)) changevalue ,
''
FROM dbo.Fund_Change f
INNER JOIN dbo.Trade_User u ON f.userId = u.userId
INNER JOIN dbo.Trade_FundInfo c ON f.userId = c.userId
WHERE u.[state] = '1'
AND c.[state] = '2'
AND f.opertime BETWEEN @BeginDate
AND @EndDate
+ @AgentId
AND f.reason IN ( '3', '4', '5', '6' ) --为表最后增加一个合计
) a
--3.用BCP命令导出到指定的文件夹下
SET @sqlCmd = 'bcp JinTong_JYRJ.dbo.TempCOrder out F:\' + @ReportFile + ' -c -U"jintongjy" -P"jtjiaoyirj!"' ;
EXEC master..xp_cmdshell @sqlCmd ;
IF EXISTS ( SELECT *
FROM sys.tables
WHERE name = 'TempCOrder' )
DROP TABLE TempCOrder ;
SET @Result = '资金变动报表查询成功' ;
RETURN 0 ;
END TRY
BEGIN CATCH
SET @Result = '资金变动报表查询失败' ;
IF EXISTS ( SELECT *
FROM sys.tables
WHERE name = 'TempCOrder' )
DROP TABLE TempCOrder ;
RETURN 1 ;
END CATCH
END