文件导入导出SQL

txt文件导入

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--删除表内容
delete Register
---导入字符串
insert dbo.Register
select bgdate,compno,cardno,identitytype,
replace(identifyno,'@','') as identifyno,
replace(compname,'"','') as compname,
telphno1,comptype,cardstatus from
OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=d:/;',
'select *from [2010-05-20.txt]
where compno is not null')

---关闭
exec sp_configure 'show advanced options',0
reconfigure

  -- txt文件导入

insert into
OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=d:/;',
'select *from [22.txt] ')
select * from Products

 

 -- txt文件查询  
  select   *   from    
  OPENROWSET('MICROSOFT.JET.OLEDB.4.0'  
  ,'Text;HDR=NO;DATABASE=d:/'  
  ,aa#txt)   
    
  -- Excel查询   
select *  from
   OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
   ,'Excel 8.0;HDR=YES;IMEX=2;DATABASE=e:/card.xls',[sheet1$])

//Excel文件导入

insert into
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 8.0;HDR=YES;DATABASE=e:/22.xls',[sheet1$])
select * from kfxt.dbo.Users

//Excel文件导出

insert Gas

select * from
OpenRowset('MICROSOFT.JET.OLEDB.4.0'
,'Excel 8.0;HDR=YES;DATABASE=e:/22.xls',[sheet1$])

//常见错误

一.连接不上，一般是出错连接

检查路径，表名，页名sheet1$是否正确

二.配置问题

1.外围配置---开始，SQL----配置工具--------功能的外围应用配置器---启用openrowset和opendatasource支持

2.对象资源管理器----选择服务器对象----链接服务器，访问端口，选择接口，点击右键，属性，把允许进程内的勾去掉