Question

delphi用adoquery 执行两个sql

实现功能是先清空某个数据库中的表，再从另个库中向空表写入数据。下面的代码是复制表的。请问如何在此之前清空edit9里写的表？
sql.Clear;
sql.Add('insert into '+edit9.text);
sql.Add('select a.* ');
ss_sql:='FROM OPENROWSET('+char(39)+'SQLOLEDB'+char(39)+','+char(39)+edit1.Text+char(39)+';'+char(39)+edit2.text+char(39)+';'+char(39)+maskedit1.Text+char(39)+',';
ss_sql:=ss_sql+char(39)+'SELECT * FROM '+edit3.text+'.'+'dbo.'+edit8.text+' where InsertTime>='+ss_time+char(39)+') as a';
sql.Add(ss_sql);
try
execsql;
memo1.Lines.Append(datetimetostr(now)+' 成功复制['+ss_time+']以后数据!');
except
memo1.Lines.Append(datetimetostr(now)+' 复制['+ss_time+']以后数据失败！');
end;
storetime:=now;
这个只能实现把表清空，不能执行insert操作了？

Answer 1

var sql1,sql2:String;
sql1 := 'delete from '+edit9.text';

ss_sql:='FROM OPENROWSET('+char(39)+'SQLOLEDB'+char(39)+','+char(39)+edit1.Text+char(39)+';'+char(39)+edit2.text+char(39)+';'+char(39)+maskedit1.Text+char(39)+',';
ss_sql:=ss_sql+char(39)+'SELECT * FROM '+edit3.text+'.'+'dbo.'+edit8.text+' where InsertTime>='+ss_time+char(39)+') as a';
sql2 :='insert into '+edit9.text+' select a.* '+ ss_sql;

try
sql.Text:=sql1;
//删除
execsql;
sql.Text:=sql2;
//插入
execsql;
memo1.Lines.Append(datetimetostr(now)+' 成功复制['+ss_time+']以后数据!');
except
memo1.Lines.Append(datetimetostr(now)+' 复制['+ss_time+']以后数据失败！');
end;

....
大致如上,楼上的代码少了一行execsql;
而且是连个execsql最好是一块放在try except 中...
另外补充的是:
两条语句一块执行,建议加个事务,防止数据出现不完整..

Answer 2

sql.Clear;
sql.Add('delete from '+edit9.text);

ss_sql:='FROM OPENROWSET('+char(39)+'SQLOLEDB'+char(39)+','+char(39)+edit1.Text+char(39)+';'+char(39)+edit2.text+char(39)+';'+char(39)+maskedit1.Text+char(39)+',';
ss_sql:=ss_sql+char(39)+'SELECT * FROM '+edit3.text+'.'+'dbo.'+edit8.text+' where InsertTime>='+ss_time+char(39)+') as a';
sql.Add(ss_sql);
try
execsql;
memo1.Lines.Append(datetimetostr(now)+' 成功复制['+ss_time+']以后数据!');
except
memo1.Lines.Append(datetimetostr(now)+' 复制['+ss_time+']以后数据失败！');
end;
storetime:=now;

Answer 3

sql.Clear;
sql.Add('delete from '+edit9.text+';');//在语句后面加个分号;

sql.Add('insert into '+edit9.text);
sql.Add('select a.* ');
ss_sql:='FROM OPENROWSET('+char(39)+'SQLOLEDB'+char(39)+','+char(39)+edit1.Text+char(39)+';'+char(39)+edit2.text+char(39)+';'+char(39)+maskedit1.Text+char(39)+',';
ss_sql:=ss_sql+char(39)+'SELECT * FROM '+edit3.text+'.'+'dbo.'+edit8.text+' where InsertTime>='+ss_time+char(39)+') as a';
sql.Add(ss_sql);
try
execsql;
memo1.Lines.Append(datetimetostr(now)+' 成功复制['+ss_time+']以后数据!');
except
memo1.Lines.Append(datetimetostr(now)+' 复制['+ss_time+']以后数据失败！');
end;
storetime:=now;