sql server与Excel的那些破事儿

 

--直接把excel导入数据库里的表
--新好excel表格，其中表格的第一行是table里的列，不会插入到table中，列与列相对应
--导入时，要把excel关闭

--先启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

--导入语句(导入现有表)
INSERT INTO testa SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=E:\test\testa.xls',sheet1$)

--导入语句(导入新表)
SELECT * INTO testc FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=E:\test\testa.xls',sheet1$)

--使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

--查看excel
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=2;DATABASE=E:\test\testa.xls',Sheet1$)

--把Excel里的数据更新到数据库里
UPDATE testa  SET aNum=b.aNum --更新数据 
from (select *from--来源数据 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 
'Excel 5.0;HDR=YES;IMEX=2;DATABASE=E:\test\testa.xls',Sheet1$) 
) as b 
where  testa.aid=b.aid--条件 

--SQL查询数据到Excel中(不含列名)(会覆盖原表)
EXEC   master..xp_cmdshell   'bcp   test.dbo.testa   out   E:\temp1.xls   -c   -q   -S "20110827-1410"   -U "sa"   -P "" '

EXEC   master..xp_cmdshell   'bcp   "SELECT cid from test.dbo.testc"   queryout   E:\test\testc.xls   -c   -S"20110827-1410"   -U "sa"   -P ""'

--遇错开启组件
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;