异构数据库之间的导入导出示例

异构数据库之间的导入导出示例,abc为表,包含以下导入与导出
MSSQL2EXCEL EXCEL2MSSQL  MSSQL2VFP VFP2MSSQL
MSSQL2access access2MSSQL  excel2access access2excel
,txt2MSSQL,MSSQL2txt
实现部分

procedure TForm1.btn_MSSQL2EXCELClick(Sender: TObject);
begin
//MSSQL2EXCEL
ADOConnection1.ConnectionString:=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:mysmallexeexcel2sqlyp.xls;'
+'Extended Properties=excel 8.0';
ADOConnection1.Execute('select * into [Abc] from drug_yk IN [ODBC] [ODBC;Driver=SQL Server;UID=sa;PWD=kcsoft58;Server=127.0.0.1;DataBase=kcsoft_his]');
//把一个MSSQL的数据库表及数据导出到excel中,abc为excel中不存在的表名,drug_yk为MSSQL里的表, sa为用户,  kcsoft58为密码 , 127.0.0.1为服务大地址 ,kcsoft_his是数据库名称
ADOConnection1.Execute('insert into [abc] select * from drug_yk in [ODBC] [ODBC;Driver=SQL Server;UID=sa;PWD=kcsoft58;Server=127.0.0.1;DataBase=kcsoft_his]');
//把一个MSSQL的数据库表的记录增加到到excel中,执行以后相当于两倍量的数据,一次是导入,一次是insert
end;


procedure TForm1.btn_EXCEL2MSSQLClick(Sender: TObject);
begin
//EXCEL2MSSQL
  ADOConnection1.ConnectionString:='Provider=SQLOLEDB.1;Password=KCSOFT58;Persist Security Info=True;User ID=SA;Initial Catalog=KCSOFT_HIS;Data Source=CHEN';
   ADOConnection1.Execute('SELECT * into [abc] FROM OpenDataSource( '
   +QuotedStr('Microsoft.Jet.OLEDB.4.0')+','
   +QuotedStr('Data Source="G:mysmallexeexcel2sqlyp.xls";Extended Properties=excel 8.0')+')...[Abc]');
end;


procedure TForm1.btn_MSSQL2VFPClick(Sender: TObject);
begin
//MSSQL2VFP
  ADOConnection2.ConnectionString:=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:mysmallexeexcel2sql;'
+'Extended Properties=dBase 5.0';
ADOConnection2.Execute('select * into Abc.dbf from drug_yk IN [ODBC] [ODBC;Driver=SQL Server;UID=sa;PWD=kcsoft58;Server=127.0.0.1;DataBase=kcsoft_his]');
//把一个MSSQL的数据库表及数据导出到VFP中,Abc.dbf 为VFP中不存在的表名,drug_yk为MSSQL里的表, sa为用户,  kcsoft58为密码 , 127.0.0.1为服务大地址 ,kcsoft_his是数据库名称

end;


procedure TForm1.btn_VFP2MSSQLClick(Sender: TObject);
begin
//VFP2MSSQL
   ADOConnection2.ConnectionString:='Provider=SQLOLEDB.1;Password=KCSOFT58;Persist Security Info=True;User ID=SA;Initial Catalog=KCSOFT_HIS;Data Source=CHEN';
   ADOConnection2.Execute('SELECT * into [abc] FROM OpenDataSource( '
   +QuotedStr('Microsoft.Jet.OLEDB.4.0')+','
   +QuotedStr('Data Source="G:mysmallexeexcel2sql";Extended Properties=dBase 5.0')+')...[Abc]');
end;



procedure TForm1.btn_MSSQL2accessClick(Sender: TObject);
begin
 //MSSQL2access
     ADOConnection2.ConnectionString:=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:mysmallexeexcel2sqlServer.MDB;'
+'Persist Security Info=False;Jet OLEDB:Database Password=happynewyear';
ADOConnection2.Execute('select * into Abc from drug_yk IN [ODBC] [ODBC;Driver=SQL Server;UID=sa;PWD=kcsoft58;Server=127.0.0.1;DataBase=kcsoft_his]');

end;

procedure TForm1.btn_access2MSSQLClick(Sender: TObject);
begin
//access2MSSQL
  ADOConnection2.ConnectionString:='Provider=SQLOLEDB.1;Password=KCSOFT58;Persist Security Info=True;User ID=SA;Initial Catalog=KCSOFT_HIS;Data Source=CHEN';
   ADOConnection2.Execute('SELECT * into [abc] FROM OpenDataSource( '
   +QuotedStr('Microsoft.Jet.OLEDB.4.0')+','
   +QuotedStr('Data Source="G:mysmallexeexcel2sqlServer.MDB";Jet OLEDB:Database Password=happynewyear')+')...[Abc]');

end;

procedure TForm1.btn_excel2accessClick(Sender: TObject);
begin
//excel2access
    ADOConnection2.ConnectionString:=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:mysmallexeexcel2sqlServer.MDB;'
+'Persist Security Info=False;Jet OLEDB:Database Password=happynewyear';
ADOConnection2.Execute('SELECT * into [abc] FROM [excel 8.0;database=G:mysmallexeexcel2sqlyp.xls].[abc$]');
end;

procedure TForm1.btn_access2excelClick(Sender: TObject);
begin
//access2excel
   ADOConnection2.ConnectionString:=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:mysmallexeexcel2sqlyp.xls;'
+'Extended Properties=excel 8.0';
ADOConnection2.Execute('SELECT * into abc  from [G:mysmallexeexcel2sqlServer.MDB;pwd=happynewyear].abc');

end;

补充:
procedure TForm1.BitBtn1Click(Sender: TObject);
begin
//MSSQL2txt
 ADOConnection2.ConnectionString:=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:mysmallexeexcel2sql;'
+'Extended Properties=text';
ADOConnection2.Execute('select * into Abc#txt from drug_yk IN [ODBC] [ODBC;Driver=SQL Server;UID=sa;PWD=kcsoft58;Server=127.0.0.1;DataBase=kcsoft_his]');

end;

procedure TForm1.BitBtn2Click(Sender: TObject);
begin
//txt2MSSQL
   ADOConnection2.ConnectionString:='Provider=SQLOLEDB.1;Password=KCSOFT58;Persist Security Info=True;User ID=SA;Initial Catalog=KCSOFT_HIS;Data Source=CHEN';
    ADOConnection2.Execute('SELECT * into [abc] FROM OpenDataSource( '
   +QuotedStr('Microsoft.Jet.OLEDB.4.0')+','
   +QuotedStr('Data Source="G:mysmallexeexcel2sql";Extended Properties=text')+')...[Abc#txt]');
end;
txt导入SQLServer
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=c:temp;','select * from aaaa.txt')

//access2access
ADOConnection2.ConnectionString:=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:mysmallexeexcel2sqlServer.MDB;'
+'Persist Security Info=False;Jet OLEDB:Database Password=happynewyear';
ADOConnection2.Execute('SELECT * into abc  from [G:mysmallexeexcel2sqlServer.MDB;pwd=happynewyear].abc');

异构数据库之间完全可以用SQL语句导数据。
告诉你一个最快的方法,用SQLServer连接DBF
在SQLServer中执行
SELECT * into bmk
FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
‘Data Source=”e:share”;User ID=Admin;Password=;Extended
properties=dBase 5.0’)…bmk
这样就可以把e:share中的bmk.dbf表导入到SQLserver中,速度是最快

在Form上放一个ADOConnection,连结指向目标Access库比如txt文件在c:tempaaaa.txt
就执行
ADOConnection.Connected := True;
ADOConnection.Execute(‘Select * Into abcd From
[Text;Database=c:temp].aaaa.txt’);

txt导入SQLServer
select * from OpenRowset(‘MSDASQL’, ‘Driver={Microsoft Text Driver
(*.txt; *.csv)};
DefaultDir=c:temp;’,’select * from aaaa.txt’)

把excel导入到Access中,同txt类似
select * into <table name> from [excel
8.0;database=<filename>].[<sheetname>$]

excel联接ADO串
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:tempa.xls;Extended
Properties=”excel 8.0;HDR=Yes;”;Persist Security Info=False

Extended Properties参数属性这样写 excel 5.0;HDR=YES;IMEX=1
IMEX=1就是指混合型转换为文本

SQLServer从excel取数
SELECT *
FROM OpenDataSource(‘Microsoft.Jet.OLEDB.4.0′,’Data
Source=”C:tempb.xls”;Extended Properties=”excel
5.0;HDR=Yes;”;Persist Security Info=False’)…sheet1$

c:tempaaa.dbf to Access,
ADOConnection 指向Access
语句是 select * into bbb from aaa in ‘c:temp’ ‘dbase 5.0;’

SQL server导出到Access
ADOConnection 指向Access
SELECT * into table FROM Tab1 IN [ODBC]
[ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=Demo;]

SQL Server表导出和txt导入一个道理基本上都是用Jet4.0 和 ODBC连结实现
先用用Jet4.0连结dbf,Access,txt然后利用SQL语句导出导入在此我就不详细说了,一句话,都可以的
列出连结串属性Extended properties,
DBase 5.0
DBASE IV
FoXPro 3.0
Paradox 7.X
excel 8.0
text;HDR=YES;FMT=Delimited

SELECT * into aaa
FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
‘Data Source=”c:temp”;User ID=Admin;Password=;Extended
properties=Text’)…[bb#txt]