写一个存储过程就轻松搞定啦,速度快、又简单,不过如果两个库在不同的机器必须用sp_addlinkedserver 建立连接。insert into 1库.dbo.表名 select * from 2库.dbo.表名
to: forgetwang() 我这个表有40个字段、约2000条记录!是不是很繁杂? 速度是不是会很慢?这个也算复杂呀?我的表都有几千万条记录的! 实在不行就一条一条来了
直接用SQL的工具import and export data传送不就行了? 如果每要传送,可以在SQL建立一个JOB定时传送,很方便的。
procedure TFUpLoad.FormShow(Sender: TObject); begin cmbmonth.Items:=g_monlist; cmbmonth.ItemIndex:=12; try dm.dlserver.Connected:=true; statusbar1.Panels[1].Text:='已连接上远程服务器!'; btnUpLoad.Enabled:=true; dm.Q2.Close; dm.Q2.Connection:=dm.dlServer; dm.Q2.DataSource:=dm.DQ1; PageControl1Change(self); except statusbar1.Panels[1].Text:='未建立与远程服务器的连接!'; btnUpLoad.Enabled:=false; end; procedure TFUpLoad.btnUpLoadClick(Sender: TObject); begin with dm do begin label1.Caption:='正在上载中....'; if not checkbox1.Checked then begin case pagecontrol1.ActivePageindex of 0:begin q2.Close; q2.SQL.Clear; q2.SQL.Add('insert into amm values(:ID,:Yh_ID,:Yh_Name,:Amonth,:Amm_Prior,:Amm_Cur,:ele,:Tran_Lose,:Cop_Lose,:Wire_Lose,:Sum_ele,:Price,:ele_Fee,:Benefit_Fee,:Latefee_Start,:Amm_Date,:Amm_Man,:Acroom_ID,:Lock,:Lock_Reason,:UpLoad_Is,:Re)'); q1.First; while not q1.Eof do begin q2.ExecSQL; q1.Next; end;//while not q1.eof end;//0 1:begin q2.Close; q2.SQL.Clear; q2.SQL.Add('insert into dlamm values(:Yh_ID,:Yh_Name,:Amonth,:AmmA_Prior,:AmmB_Prior,:AmmC_Prior,:AmmA_Cur,:AmmB_Cur,:AmmC_Cur,'); q2.SQL.Add(':ele,:Tran_Lose,:Cop_Lose,:Wire_Lose,:Sum_ele,:Price,:ele_Fee,:Benefit_Fee,:Amm_Date,:Amm_Man,:Acroom_ID,:Lock,:Lock_Reason,:Re,:UpLoad_Is,:Latefee_Start'); q1.First; while not q1.Eof do begin q2.ExecSQL; q1.Next; end;//while not q1.eof end;//1 2:begin q2.Close; q2.SQL.Clear; q2.SQL.Add('insert into fee values(:Yh_ID,:Yh_Name,:Amonth,:Amm_Prior,:Amm_Cur,:ele,:Tran_Lose,:Cop_Lose,:Wire_Lose,:Sum_ele,:Price,:ele_Fee,:Benefit_Fee,'); q2.SQL.Add(':Price_Type,:ele_Price,:Add_Price,:Benefit_Type,:Benefit_Price,:Latefee_Start,:Latefee_End,:Latefee_Date,:Latefee,:amm_ID,:Capital,:Amm_Date,:Amm_Man,:Acroom_ID,:Lock,:Lock_Reason,:Re,:UpLoad_Is,:Latefee_Start'); q1.First; while not q1.Eof do begin q2.ExecSQL; q1.Next; end;//while not q1.eof end;//2 3:begin q2.Close; q2.SQL.Clear; q2.SQL.Add('insert into dlfee values(:Yh_ID,:Yh_Name,:Amonth,:AmmA_ID,'); q2.SQL.Add(':AmmB_ID,:AmmC_ID,:AmmA_Prior,:AmmB_Prior,:AmmC_Prior,:AmmA_Cur,'); q2.SQL.Add(':AmmB_Cur,:AmmC_Cur,:ele,:Tran_Lose,:Cop_Lose,:Wire_Lose,:Sum_ele,'); q2.SQL.Add(':Price_Type,:Price,:ele_Price,:Add_Price,:ele_Fee,:Benefit_Type,:Benefit_Price,:Benefit_Fee,:Latefee_Start,:Latefee_End,:Latefee_Date,:Latefee,:Sum_Fee,:Capital,:Pay_Date,:Pay_Acman,:Pay_Acroom,:Re,:UpLoad_Is'); q1.First; while not q1.Eof do begin q2.ExecSQL; q1.Next; end;//while not q1.eof end;//3 4:begin q2.Close; q2.SQL.Clear; q2.SQL.Add(':Alarm_Time,:Alarm_addr,:Op_Name,:Yh_ID,:Alarm_Events,:Alarm_reason,:Acroom_ID,:Amonth,:Alarm_Is,:UpLoad_Is'); q1.First; while not q1.Eof do begin q2.ExecSQL; q1.Next; end;//while not q1.eof end;//4 5:begin q2.Close; q2.SQL.Clear; q2.SQL.Add('insert into Account values(:Yh_ID,:Yh_Name,:Account_In,:Account_Time,:Account_Cur,:Amonth,:Op_Name,:acroom_ID,:Events,:UpLoad_Is,:Yh_Type,:Active'); q1.First; while not q1.Eof do begin q2.ExecSQL; q1.Next; end;//while not q1.eof end;//5 6:begin q2.Close; q2.SQL.Clear; q2.SQL.Add('insert into Op_fee_Record values(:Op_Time,:Op_Addr,:Op_Name,:Op_Events,:Yh_ID,:Amonth,:acroom_ID,:Re,:UpLoad_Is'); q1.First; while not q1.Eof do begin q2.ExecSQL; q1.Next; end;//while not q1.eof end;//6 end;//case end//not checkbox1.checked end;//with dm do label1.Caption:='上载完成!'; procedure TFUpLoad.PageControl1Change(Sender: TObject); begin with dm.Q1 do begin case pagecontrol1.ActivePageIndex of 0:begin close; sql.Clear; sql.Add('select ID,Yh_ID,Yh_Name,Amonth,Amm_Prior,Amm_Cur,ele,Tran_Lose,Cop_Lose,Wire_Lose,Sum_ele,Price,ele_Fee,Benefit_Fee,Latefee_Start,Amm_Date,Amm_Man,Acroom_ID,Lock,Lock_Reason,UpLoad_Is,Re '); sql.Add('from amm where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')'); open; end;//0 1:begin close; sql.Clear; sql.Add('select * from dlamm where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')'); open; end;//1 dlamm 2:begin close; sql.Clear; sql.Add('select * from fee where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')'); open; end;//2 fee 3:begin close; sql.Clear; sql.Add('select * from dlfee where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')'); open; end;//3 fee 4:begin close; sql.Clear; sql.Add('select * from Alarm where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')'); open; end;//4 5:begin close; sql.Clear; sql.Add('select * from Account where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')'); open; end;//5 6:begin close; sql.Clear; sql.Add('select * from Op_fee_record where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')'); open; end;//6 end;//case end;//dm.q1 end; procedure TFUpLoad.FormClose(Sender: TObject; var Action: TCloseAction); begin dm.dlServer.Connected:=false; dm.Q2.Close; dm.Q2.Connection:=dm.dlconnection; dm.Q2.DataSource:=nil; action:=cafree; end;
你的方法只适合开发人员,不适合于用户。
是要实现‘一键宕数据’的功能啊!就是BDE中的BATCHMOVE
:我是想在我的软件中实现的啊,不是手工导入。
to:chll(霖子)
我整个软件框架全是基于ADO连接的!不可能用BDE;
我这个表有40个字段、约2000条记录!是不是很繁杂?
速度是不是会很慢?
连接s2.dl.表1),用post方法提交;
行不行??????各位有什么看法!(象forgetwang()说的一样)
select * from 2库.dbo.表名
我这个表有40个字段、约2000条记录!是不是很繁杂?
速度是不是会很慢?这个也算复杂呀?我的表都有几千万条记录的!
实在不行就一条一条来了
如果每要传送,可以在SQL建立一个JOB定时传送,很方便的。
begin
cmbmonth.Items:=g_monlist;
cmbmonth.ItemIndex:=12;
try
dm.dlserver.Connected:=true;
statusbar1.Panels[1].Text:='已连接上远程服务器!';
btnUpLoad.Enabled:=true;
dm.Q2.Close;
dm.Q2.Connection:=dm.dlServer;
dm.Q2.DataSource:=dm.DQ1;
PageControl1Change(self);
except
statusbar1.Panels[1].Text:='未建立与远程服务器的连接!';
btnUpLoad.Enabled:=false;
end;
procedure TFUpLoad.btnUpLoadClick(Sender: TObject);
begin
with dm do
begin
label1.Caption:='正在上载中....';
if not checkbox1.Checked then
begin
case pagecontrol1.ActivePageindex of
0:begin
q2.Close;
q2.SQL.Clear;
q2.SQL.Add('insert into amm values(:ID,:Yh_ID,:Yh_Name,:Amonth,:Amm_Prior,:Amm_Cur,:ele,:Tran_Lose,:Cop_Lose,:Wire_Lose,:Sum_ele,:Price,:ele_Fee,:Benefit_Fee,:Latefee_Start,:Amm_Date,:Amm_Man,:Acroom_ID,:Lock,:Lock_Reason,:UpLoad_Is,:Re)');
q1.First;
while not q1.Eof do
begin
q2.ExecSQL;
q1.Next;
end;//while not q1.eof
end;//0
1:begin
q2.Close;
q2.SQL.Clear;
q2.SQL.Add('insert into dlamm values(:Yh_ID,:Yh_Name,:Amonth,:AmmA_Prior,:AmmB_Prior,:AmmC_Prior,:AmmA_Cur,:AmmB_Cur,:AmmC_Cur,');
q2.SQL.Add(':ele,:Tran_Lose,:Cop_Lose,:Wire_Lose,:Sum_ele,:Price,:ele_Fee,:Benefit_Fee,:Amm_Date,:Amm_Man,:Acroom_ID,:Lock,:Lock_Reason,:Re,:UpLoad_Is,:Latefee_Start');
q1.First;
while not q1.Eof do
begin
q2.ExecSQL;
q1.Next;
end;//while not q1.eof
end;//1
2:begin
q2.Close;
q2.SQL.Clear;
q2.SQL.Add('insert into fee values(:Yh_ID,:Yh_Name,:Amonth,:Amm_Prior,:Amm_Cur,:ele,:Tran_Lose,:Cop_Lose,:Wire_Lose,:Sum_ele,:Price,:ele_Fee,:Benefit_Fee,');
q2.SQL.Add(':Price_Type,:ele_Price,:Add_Price,:Benefit_Type,:Benefit_Price,:Latefee_Start,:Latefee_End,:Latefee_Date,:Latefee,:amm_ID,:Capital,:Amm_Date,:Amm_Man,:Acroom_ID,:Lock,:Lock_Reason,:Re,:UpLoad_Is,:Latefee_Start');
q1.First;
while not q1.Eof do
begin
q2.ExecSQL;
q1.Next;
end;//while not q1.eof
end;//2
3:begin
q2.Close;
q2.SQL.Clear;
q2.SQL.Add('insert into dlfee values(:Yh_ID,:Yh_Name,:Amonth,:AmmA_ID,');
q2.SQL.Add(':AmmB_ID,:AmmC_ID,:AmmA_Prior,:AmmB_Prior,:AmmC_Prior,:AmmA_Cur,');
q2.SQL.Add(':AmmB_Cur,:AmmC_Cur,:ele,:Tran_Lose,:Cop_Lose,:Wire_Lose,:Sum_ele,');
q2.SQL.Add(':Price_Type,:Price,:ele_Price,:Add_Price,:ele_Fee,:Benefit_Type,:Benefit_Price,:Benefit_Fee,:Latefee_Start,:Latefee_End,:Latefee_Date,:Latefee,:Sum_Fee,:Capital,:Pay_Date,:Pay_Acman,:Pay_Acroom,:Re,:UpLoad_Is');
q1.First;
while not q1.Eof do
begin
q2.ExecSQL;
q1.Next;
end;//while not q1.eof
end;//3
4:begin
q2.Close;
q2.SQL.Clear;
q2.SQL.Add(':Alarm_Time,:Alarm_addr,:Op_Name,:Yh_ID,:Alarm_Events,:Alarm_reason,:Acroom_ID,:Amonth,:Alarm_Is,:UpLoad_Is');
q1.First;
while not q1.Eof do
begin
q2.ExecSQL;
q1.Next;
end;//while not q1.eof
end;//4
5:begin
q2.Close;
q2.SQL.Clear;
q2.SQL.Add('insert into Account values(:Yh_ID,:Yh_Name,:Account_In,:Account_Time,:Account_Cur,:Amonth,:Op_Name,:acroom_ID,:Events,:UpLoad_Is,:Yh_Type,:Active');
q1.First;
while not q1.Eof do
begin
q2.ExecSQL;
q1.Next;
end;//while not q1.eof
end;//5
6:begin
q2.Close;
q2.SQL.Clear;
q2.SQL.Add('insert into Op_fee_Record values(:Op_Time,:Op_Addr,:Op_Name,:Op_Events,:Yh_ID,:Amonth,:acroom_ID,:Re,:UpLoad_Is');
q1.First;
while not q1.Eof do
begin
q2.ExecSQL;
q1.Next;
end;//while not q1.eof
end;//6
end;//case
end//not checkbox1.checked
end;//with dm do
label1.Caption:='上载完成!';
procedure TFUpLoad.PageControl1Change(Sender: TObject);
begin
with dm.Q1 do
begin
case pagecontrol1.ActivePageIndex of
0:begin
close;
sql.Clear;
sql.Add('select ID,Yh_ID,Yh_Name,Amonth,Amm_Prior,Amm_Cur,ele,Tran_Lose,Cop_Lose,Wire_Lose,Sum_ele,Price,ele_Fee,Benefit_Fee,Latefee_Start,Amm_Date,Amm_Man,Acroom_ID,Lock,Lock_Reason,UpLoad_Is,Re ');
sql.Add('from amm where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')');
open;
end;//0
1:begin
close;
sql.Clear;
sql.Add('select * from dlamm where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')');
open;
end;//1 dlamm
2:begin
close;
sql.Clear;
sql.Add('select * from fee where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')');
open;
end;//2 fee
3:begin
close;
sql.Clear;
sql.Add('select * from dlfee where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')');
open;
end;//3 fee
4:begin
close;
sql.Clear;
sql.Add('select * from Alarm where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')');
open;
end;//4
5:begin
close;
sql.Clear;
sql.Add('select * from Account where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')');
open;
end;//5
6:begin
close;
sql.Clear;
sql.Add('select * from Op_fee_record where (UpLoad_Is=0) and (Amonth='''+cmbmonth.text+''')');
open;
end;//6
end;//case
end;//dm.q1
end;
procedure TFUpLoad.FormClose(Sender: TObject; var Action: TCloseAction);
begin
dm.dlServer.Connected:=false;
dm.Q2.Close;
dm.Q2.Connection:=dm.dlconnection;
dm.Q2.DataSource:=nil;
action:=cafree;
end;