在MSSQL2000中使用Transact-SQL语句处理DBF数据转换
1、查询dbf数据
SELECT * FROM OpenDataSource('vfpoledb','Data Source="d:/vfp/test.dbc"')...test1
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={Microsoft Visual FoxPro Driver};
Exclusive=No;Collate=Machine;Deleted=Yes;Null=No;
SourceDB=d:/vfp/test.dbc;SourceType=DBC;',
'SELECT * FROM test1') AS a
Select *
From Openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;
SourceDB=d:/vfp/;SourceType=DBF',
'select * from test3')
2、将DBF数据导入mssql中
SELECT * into test1 FROM OpenDataSource('vfpoledb','Data Source="d:/vfp/test.dbc"')...test1
3、将SQL数据导入dbf中
insert into OPENROWSET('MSDASQL', 'DRIVER={Microsoft Visual FoxPro Driver}; Exclusive=No;Collate=Machine;Deleted=Yes;Null=No; SourceDB=d:/vfp/test.dbc;SourceType=DBC;', 'SELECT * FROM test1') select * from Test1
4、修改数据
Update openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceDB=d:/vfp/;SourceType=DBF','select * from test3 where code="11"') set name = '"12"'
以下是我测试成功的方法
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER={Microsoft Visual FoxPro Driver};
Exclusive=No;Collate=Machine;Deleted=Yes;Null=No;
SourceDB=d:/work/vf/data1.dbc;SourceType=DBC;',
'SELECT * FROM nsnew1000200.dbf') AS a
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=d:/work/vf/',
'select * from [nsnew1000200.DBF]')
Select *
From Openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;
SourceDB=d:/work/vf/;SourceType=DBF',
'select * from nsnew1000200.dbf')