Excel数据汇出

1.EXEC master..xp_cmdshell 'bcp DataTest.dbo.AirPerformance out D:\WinForm\ProjExcel\Files\Engg_Index_DFS\Report\airperformancetol.xls -c -q -S "WUSF\DAT" -U"sa" -P"055122"'

2.EXEC master..xp_cmdshell 'bcp "select * from DataTest.dbo.AirPerformance" queryout D:\WinForm\ProjExcel\Files\Engg_Index_DFS\Report\airperformancetol.xls -c -q -S "WUSF\DAT" -U"sa" -P"055122"'

如果加字段,配合union all

3.declare @sql varchar(8000),@path varchar(8000),@tbname varchar(8000)
set @path='c:\a.xls'
set @tbname='sheet1'
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
;DATABASE='+@path+''',['+@tbname+'$])'
exec('insert into '+@sql+'(f1) select Reportno from AirPerformance ')

转载于:https://www.cnblogs.com/cnaspnet/archive/2006/07/05/443290.html