建临时表可用SELECT INTO......临时表可以建立在内存中,这样处理速度较快。内存临时表创建有如下方法: 1.使用查询控件创建临时表 第1步:在窗体上放入查询控件(TQuery),并设置好所连接的数据表。 第2步:添加如下语句: TQuery. CachedUpdates=True; TQuery. RequestLive=True。 第3步:在原有的SQL语句后加入一条Where子语句,要求加入这条Where子语句后SQL查询结果为空。 例如: SELECT Biolife."Species No", Category, Common_Name, Biolife."Species Name", Biolife."Length (cm)", Length_In, Notes, Graphic FROM "biolife.db" Biolife where Biolife.Category=′A′ and Biolife.Category=′B′ 这样临时表就建立好了。 2.使用代码创建临时表 函数代码如下: function CreateTableInMemory(const AFieldDefs:TFieldDefs): TDataSet; var TempTable:TClientDataSet; begin TempTable:=nil; Result:=nil; if AFieldDefs〈〉nil then begin try TempTable:=TClientDataSet.Create(Application); TempTable.FieldDefs.Assign(AFieldDefs); TempTable.CreateDataSet; Result:=(TempTable as TDataSet); Except if TempTable〈〉nil then TempTable.Free; Result:=nil; raise; end end end; 在程序中按如下方法调用: procedure TForm1.Button1Click(Sender: TObject); var ADataSet:TDataSet; begin ADataSet:=TDataSet.Create(Self); with ADataSet.FieldDefs do begin Add(′Name′,ftString,30,False); Add(′Value′,ftInteger,0,False); end; with DataSource1 do begin DataSet:=CreateTableInMemory(ADataSet.FieldDefs); DataSet.Open; end; ADataSet.Free; end;
declare @temptable varchar(50) set @temptable = '##temp'+cast(@@spid as varchar) exec ('create table '+@temptable+'(a1 int)') 在A,B上創建如下的觸發器:这是A表的 create trigger trigger_A on A for insert,update,delete as declare @temptable varchar(50) set @temptable = '##temp'+cast(@@spid as varchar) if object_id('tempdb.dbo.'+@temptable) is not null --在这里写上你要做的比较和分析 else rollback
如果是SQL2000,可以提供TABLE类型变量,比较合适。
请问:make11111 tmP_BB是临时表,pdk是盘点表,d_SDJK是单据的表头,d_DJK是单据的内容其中D_SDJH.DJBH=D_DJK.DJBH 现在:我要把(D_sdjk.sj>:date1) and (D_sdjk.sj<=:date2) and (D_sdjk.djlx=:djlx)的数据放出来放入临时表TMP_BB中,再按HPDM统计出数量SL的合计放入PDK库中,临时表如何生成。我在其他的模块中也要用到同一类型的临时表,谢谢 Close; SQL.Clear ; SQL.Add('insert into tmp_bb(chdm,hpdm,djlx,sl) SELECT D_sdjk.chdm,D_djk.hpdm,D_sdjk.djlx,D_djk.sl FROM D_SDJK,D_DJK '); SQL.Add('WHERE (D_sdjk.djbh=D_djk.djbh) and (D_sdjk.sj>:date1) and (D_sdjk.sj<=:date2) and (D_sdjk.djlx=:djlx) '); ParamByName('date1').value:=qdsj; ParamByName('date2').value:=xzpdsj; ParamByName('djlx').asstring:=thdjlx; ExecSQL; prog.Position:=70; //进行数据处理 Close; SQL.Clear ; SQL.Add('update Tmp_bb set sl=sl'); ExecSQL; Close; SQL.Clear ; SQL.Add('update Tmp_bb set sl=-sl where djlx=:djlx '); ParamByName('djlx').asstring:=LLdjlx; ExecSQL; prog.Position:=75; //把上月的盘点数放入 Close; SQL.Clear ; SQL.Add('insert into Tmp_bb(hpdm,qcsl) SELECT hpdm,pdsl FROM pdk where kcbb=:kcbb'); ParamByName('kcbb').asstring:=sypdbb; ExecSQL; prog.Position:=80; Close; SQL.Clear ; SQL.Add('insert into Tmp_bb(hpdm) SELECT hpdm FROM hpk where lx=:lx_'); ParamByName('lx_').asinteger:=1; ExecSQL; prog.Position:=80; //对Tmp_bb的数据进行统计商品编号 Close; SQL.Clear ; SQL.Add('insert into pdk(kcbb,hpdm,pdsl,yesl) SELECT "12",hpdm,sum(qcsl+sl),sum(qcsl+sl) FROM Tmp_bb Group by hpdm'); ExecSQL; prog.Position:=85; Close; SQL.Clear ; SQL.Add('update pdk set kcbb=:kcbb1 where kcbb="12"'); ParamByName('kcbb1').value:=bypdbb; ExecSQL; Close;
大概改一下,没有测试,关键是临时表前要加#号, 语句可以一次加多个,不用每句都EXECSQL。 Close; SQL.Clear ; SQL.Add('insert into #tmp_bb(chdm,hpdm,djlx,sl) SELECT D_sdjk.chdm,D_djk.hpdm,D_sdjk.djlx,D_djk.sl FROM D_SDJK,D_DJK '); SQL.Add('WHERE (D_sdjk.djbh=D_djk.djbh) and (D_sdjk.sj>:date1) and (D_sdjk.sj<=:date2) and (D_sdjk.djlx=:djlx1) '); ParamByName('date1').value:=qdsj; ParamByName('date2').value:=xzpdsj; ParamByName('djlx1').asstring:=thdjlx; //进行数据处理 SQL.Add('update #Tmp_bb set sl=sl'); SQL.Add('update #Tmp_bb set sl=-sl where djlx=:djlx2 '); ParamByName('djlx2').asstring:=LLdjlx; SQL.Add('insert into #Tmp_bb(hpdm,qcsl) SELECT hpdm,pdsl FROM pdk where kcbb=:kcbb'); ParamByName('kcbb').asstring:=sypdbb; SQL.Add('insert into #Tmp_bb(hpdm) SELECT hpdm FROM hpk where lx=:lx_'); ParamByName('lx_').asinteger:=1; SQL.Add('insert into pdk(kcbb,hpdm,pdsl,yesl) SELECT "12",hpdm,sum(qcsl+sl),sum(qcsl+sl) FROM Tmp_bb Group by hpdm'); ExecSQL; prog.Position:=85; Close; SQL.Clear ; SQL.Add('update pdk set kcbb=:kcbb1 where kcbb="12"'); ParamByName('kcbb1').value:=bypdbb; ExecSQL; Close;
johnsonrao(johnson) : 1、tmp_bb:如何建立临时表 2、引用临时表我知道 SQL.Add('insert into #Tmp_bb(hpdm) SELECT hpdm FROM hpk where lx=:lx_'); ParamByName('lx_').asinteger:=1; SQL.Add('insert into pdk(kcbb,hpdm,pdsl,yesl) SELECT "12",hpdm,sum(qcsl+sl),sum(qcsl+sl) FROM Tmp_bb Group by hpdm');为何这里不加# 3、有时我要从TMp_bb临时表中取数据出来分析,而且有条件,如何设置。
第1步:在窗体上放入查询控件(TQuery),并设置好所连接的数据表。
第2步:添加如下语句:
TQuery. CachedUpdates=True;
TQuery. RequestLive=True。
第3步:在原有的SQL语句后加入一条Where子语句,要求加入这条Where子语句后SQL查询结果为空。
例如:
SELECT Biolife."Species No", Category, Common_Name, Biolife."Species Name", Biolife."Length (cm)", Length_In, Notes, Graphic
FROM "biolife.db" Biolife
where Biolife.Category=′A′ and Biolife.Category=′B′
这样临时表就建立好了。
2.使用代码创建临时表
函数代码如下:
function CreateTableInMemory(const AFieldDefs:TFieldDefs):
TDataSet;
var TempTable:TClientDataSet;
begin
TempTable:=nil;
Result:=nil;
if AFieldDefs〈〉nil then
begin
try
TempTable:=TClientDataSet.Create(Application);
TempTable.FieldDefs.Assign(AFieldDefs);
TempTable.CreateDataSet;
Result:=(TempTable as TDataSet);
Except
if TempTable〈〉nil then TempTable.Free;
Result:=nil;
raise;
end
end
end;
在程序中按如下方法调用:
procedure TForm1.Button1Click(Sender: TObject);
var ADataSet:TDataSet;
begin
ADataSet:=TDataSet.Create(Self);
with ADataSet.FieldDefs do
begin
Add(′Name′,ftString,30,False);
Add(′Value′,ftInteger,0,False);
end;
with DataSource1 do
begin
DataSet:=CreateTableInMemory(ADataSet.FieldDefs);
DataSet.Open;
end;
ADataSet.Free;
end;
set @temptable = '##temp'+cast(@@spid as varchar)
exec ('create table '+@temptable+'(a1 int)')
在A,B上創建如下的觸發器:这是A表的
create trigger trigger_A on A
for insert,update,delete
as
declare @temptable varchar(50)
set @temptable = '##temp'+cast(@@spid as varchar)
if object_id('tempdb.dbo.'+@temptable) is not null
--在这里写上你要做的比较和分析 else
rollback
tmP_BB是临时表,pdk是盘点表,d_SDJK是单据的表头,d_DJK是单据的内容其中D_SDJH.DJBH=D_DJK.DJBH
现在:我要把(D_sdjk.sj>:date1) and (D_sdjk.sj<=:date2) and (D_sdjk.djlx=:djlx)的数据放出来放入临时表TMP_BB中,再按HPDM统计出数量SL的合计放入PDK库中,临时表如何生成。我在其他的模块中也要用到同一类型的临时表,谢谢
Close;
SQL.Clear ;
SQL.Add('insert into tmp_bb(chdm,hpdm,djlx,sl) SELECT D_sdjk.chdm,D_djk.hpdm,D_sdjk.djlx,D_djk.sl FROM D_SDJK,D_DJK ');
SQL.Add('WHERE (D_sdjk.djbh=D_djk.djbh) and (D_sdjk.sj>:date1) and (D_sdjk.sj<=:date2) and (D_sdjk.djlx=:djlx) ');
ParamByName('date1').value:=qdsj;
ParamByName('date2').value:=xzpdsj;
ParamByName('djlx').asstring:=thdjlx;
ExecSQL;
prog.Position:=70; //进行数据处理
Close;
SQL.Clear ;
SQL.Add('update Tmp_bb set sl=sl');
ExecSQL;
Close;
SQL.Clear ;
SQL.Add('update Tmp_bb set sl=-sl where djlx=:djlx ');
ParamByName('djlx').asstring:=LLdjlx;
ExecSQL;
prog.Position:=75;
//把上月的盘点数放入
Close;
SQL.Clear ;
SQL.Add('insert into Tmp_bb(hpdm,qcsl) SELECT hpdm,pdsl FROM pdk where kcbb=:kcbb');
ParamByName('kcbb').asstring:=sypdbb;
ExecSQL;
prog.Position:=80; Close;
SQL.Clear ;
SQL.Add('insert into Tmp_bb(hpdm) SELECT hpdm FROM hpk where lx=:lx_');
ParamByName('lx_').asinteger:=1;
ExecSQL;
prog.Position:=80; //对Tmp_bb的数据进行统计商品编号
Close;
SQL.Clear ;
SQL.Add('insert into pdk(kcbb,hpdm,pdsl,yesl) SELECT "12",hpdm,sum(qcsl+sl),sum(qcsl+sl) FROM Tmp_bb Group by hpdm');
ExecSQL;
prog.Position:=85;
Close;
SQL.Clear ;
SQL.Add('update pdk set kcbb=:kcbb1 where kcbb="12"');
ParamByName('kcbb1').value:=bypdbb;
ExecSQL;
Close;
语句可以一次加多个,不用每句都EXECSQL。 Close;
SQL.Clear ;
SQL.Add('insert into #tmp_bb(chdm,hpdm,djlx,sl) SELECT D_sdjk.chdm,D_djk.hpdm,D_sdjk.djlx,D_djk.sl FROM D_SDJK,D_DJK ');
SQL.Add('WHERE (D_sdjk.djbh=D_djk.djbh) and (D_sdjk.sj>:date1) and (D_sdjk.sj<=:date2) and (D_sdjk.djlx=:djlx1) ');
ParamByName('date1').value:=qdsj;
ParamByName('date2').value:=xzpdsj;
ParamByName('djlx1').asstring:=thdjlx; //进行数据处理
SQL.Add('update #Tmp_bb set sl=sl'); SQL.Add('update #Tmp_bb set sl=-sl where djlx=:djlx2 ');
ParamByName('djlx2').asstring:=LLdjlx; SQL.Add('insert into #Tmp_bb(hpdm,qcsl) SELECT hpdm,pdsl FROM pdk where kcbb=:kcbb');
ParamByName('kcbb').asstring:=sypdbb; SQL.Add('insert into #Tmp_bb(hpdm) SELECT hpdm FROM hpk where lx=:lx_');
ParamByName('lx_').asinteger:=1; SQL.Add('insert into pdk(kcbb,hpdm,pdsl,yesl) SELECT "12",hpdm,sum(qcsl+sl),sum(qcsl+sl) FROM Tmp_bb Group by hpdm');
ExecSQL;
prog.Position:=85;
Close;
SQL.Clear ;
SQL.Add('update pdk set kcbb=:kcbb1 where kcbb="12"');
ParamByName('kcbb1').value:=bypdbb;
ExecSQL;
Close;
1、tmp_bb:如何建立临时表
2、引用临时表我知道
SQL.Add('insert into #Tmp_bb(hpdm) SELECT hpdm FROM hpk where lx=:lx_');
ParamByName('lx_').asinteger:=1; SQL.Add('insert into pdk(kcbb,hpdm,pdsl,yesl) SELECT "12",hpdm,sum(qcsl+sl),sum(qcsl+sl) FROM Tmp_bb Group by hpdm');为何这里不加#
3、有时我要从TMp_bb临时表中取数据出来分析,而且有条件,如何设置。
filename:string;//临时表文件名
source,target: TFileStream;
begin
source:=TFileStream.Create('源库文件名',fmOpenRead );//支持路径、扩展名;
try
target:=TFileStream.Create(filename,fmOpenWrite or fmCreate);
try
if target.CopyFrom(source,source.Size)=source.Size then
ShowMessage('成功创建临时表+filename+'!!!');
finally
target.Free;
end;
finally
source.Free;
end;