BCP xp_cmdshell

SQL Server 数据导出技巧

最新推荐文章于 2025-05-15 17:15:52 发布

weixin_30713953

最新推荐文章于 2025-05-15 17:15:52 发布

阅读量226

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/aspsmile/archive/2008/08/05/1260782.html

本文详细介绍如何使用SQL Server的xp_cmdshell存储过程配合BCP命令，从现有表及全局临时表导出数据到Excel文件的方法。同时，文章还探讨了在导出过程中可能遇到的错误及其解决方案，例如双引号与查询语句之间的空格问题。

---------------------- 从现有表导出结果 ----------------------
-- 将AdventureWorks.HumanResources.Employee表的数据导出到C:\test.xls
EXEC master..xp_cmdshell ' BCP "select * from AdventureWorks.HumanResources.Employee" queryout C:\test.xls -c -SCN110075 -Usa -Pchang'

-- 删除C:\test.xls
EXEC master..xp_cmdshell 'del C:\test.xls '

---------------------- 从全局临时表表导出结果 ----------------------
-- 建立临时表
select row_number() over(order by employeeId) as rowNumber, * into ##tmp from AdventureWorks.HumanResources.Employee

-- 从临时表导出数据
EXEC master..xp_cmdshell ' BCP "select * from tempdb..##tmp " queryout C:\test.xls -c -SCN110075 -Usa -Pchang'

-- 删除临时表
drop table ##tmp

-- 删除C:\test.xls
EXEC master..xp_cmdshell 'del C:\test.xls '

---------------------- 这样写有错，还为找到原因 ----------------------
EXEC master..xp_cmdshell ' BCP "HumanResources.Employee " out C:\test.xls -c -SCN110075 -Usa -Pchang'

-- 错误信息
/*
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
An error occurred while processing the command line.
NULL
*/

---------------------- union 好像双引号与查询语句之间不能有空格 ----------------------
EXEC master..xp_cmdshell 'BCP " select
"''ForeCastType''" as ForeCastType, "''OrderNumber''" as OrderNumber,
"''HHPN''" as HHPN, "''Plant''" as Plant, "''Quantity''" as Quantity, "''DueDate''" as DueDate
union
select "''LSF''" as ForeCastType, "''FCST2006''" as OrderNumber, HHPN, Plant,
convert(varchar(10),Quantity) as Quantity, convert(char(10), DueDate, 20) as DueDate
from Converter.dbo.detail
where pid=1 order by plant, HHPN, DueDate "
queryout c:\output.xls -c -SCN110075 -Usa -Pchang'

转载于:https://www.cnblogs.com/aspsmile/archive/2008/08/05/1260782.html