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.对象资源管理器----选择服务器对象----链接服务器,访问端口,选择接口,点击右键,属性,把允许进程内的勾去掉