异构数据库之间的导入导出
//mssql2excel
adoconnection1.connectionstring:=
'provider=microsoft.jet.oledb.4.0;data source=g:/mysmallexe/excel2sql/yp.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
//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:/mysmallexe/excel2sql/yp.xls";extended properties=excel 8.0')
+')...[abc]');
//mssql2vfp
adoconnection2.connectionstring:=
'provider=microsoft.jet.oledb.4.0;data source=g:/mysmallexe/excel2sql;'
+'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是数据库名称
//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:/mysmallexe/excel2sql";extended properties=dbase 5.0')+')...
[abc]');
//mssql2access
adoconnection2.connectionstring:=
'provider=microsoft.jet.oledb.4.0;data source=g:/mysmallexe/excel2sql/server.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]');
//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:/mysmallexe/excel2sql/server.mdb";jet oledb:database
password=happynewyear')+')...[abc]');
//excel2access
adoconnection2.connectionstring:=
'provider=microsoft.jet.oledb.4.0;data source=g:/mysmallexe/excel2sql/server.mdb;'
+'persist security info=false;jet oledb:database password=happynewyear';
adoconnection2.execute('select * into [abc] from [excel
8.0;database=g:/mysmallexe/excel2sql/yp.xls].[abc$]');
//access2excel
adoconnection2.connectionstring:=
'provider=microsoft.jet.oledb.4.0;data source=g:/mysmallexe/excel2sql/yp.xls;'
+'extended properties=excel 8.0';
adoconnection2.execute('select * into abc from
[g:/mysmallexe/excel2sql/server.mdb;pwd=happynewyear].abc');
//mssql2txt
adoconnection2.connectionstring:=
'provider=microsoft.jet.oledb.4.0;data source=g:/mysmallexe/excel2sql;'
+'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]');
//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:/mysmallexe/excel2sql";extended properties=text')+')...<
新闻热点
疑难解答