Sql Server导入导出Excel

原文：http://uniquewu.iteye.com/blog/643779

 

导入代码：

 

excelexec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
go
declare @ttable varchar(20)
set @ttable='newtable'
if exists (select 1 from sysobjects where name=@ttable and xtype='u')
exec('drop table ' + @ttable)

SELECT * into #newtable 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
  'Data Source="c:\text.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 
exec('select * into ' + @ttable +' from #newtable')
drop table #newtable
--   使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure 
go

导出代码：

EXEC master..xp_cmdshell 'bcp 库名.dbo.表名 out "导出路径" /c -/S"deepinxp" /U"SQL登陆用户" -P"密码"'