Allan


Something along the lines of

SELECT * INTO "Memory\tbl" FROM DiskTable
WHERE True = False

The magic is in the WHERE clause which guarantees no data transfered.

Note that this doesn't create any indices.

Personally I created a function (below) to copy the structure.

Roy Lambert


procedure SelfMakeMemoryTable(WhichTable, CloneTable: TDBISAMTable; SetExclusive, AndOpen: boolean; const MakSesh: TDBISAMSession);
var
Cntr: integer;
Maker: TDBISAMQuery;
// MakerSession:TDBISAMSession;
iFlds: string;
tblName: string;
idxFlds: TStringList;
idxDesc: TStringList;
const
dq = '"';
function GetDefaultExpression(Fld: TField): string;
begin
 Result := '';
 if Fld.DefaultExpression <> '' then begin
  Result := ' DEFAULT ' + Fld.DefaultExpression;
 end;
end;
function GetFieldType(Fld: TField): string;
begin
 case Fld.DataType of
  ftInteger: Result := 'INTEGER';
  ftString, ftFixedChar: Result := 'VARCHAR(' + IntToStr(Fld.Size) + ')';
  ftBoolean: Result := 'BOOLEAN';
  ftDate: Result := 'DATE';
  ftFloat: Result := 'FLOAT';
  ftTime: Result := 'TIME';
  ftMemo: Result := 'MEMO';
  ftBlob: Result := 'BLOB';
  ftGraphic: Result := 'GRAPHIC';
  ftFmtMemo: Result := 'BLOB(0,3)';
  ftTypedBinary: Result := 'BLOB(0,7)';
  ftDateTime: Result := 'TIMESTAMP';
  ftSmallInt: Result := 'SMALLINT';
  ftWord: Result := 'WORD';
  ftAutoInc: Result := 'AUTOINC';
  ftLargeInt: Result := 'LARGEINT';
  ftCurrency: Result := 'MONEY';
  ftBCD: Result := 'DECIMAL(0 , 4)';
  ftBytes: Result := 'BYTES(' + IntToStr(Fld.Size) + ')';
 else Result := 'VARCHAR(' + IntToStr(Fld.Size) + ')';
 end;
end;
procedure GetIdxInfo(FldList, DescList: string; DescWanted: boolean);
var
 lCntr: integer;
begin
 idxFlds.Clear;
 idxDesc.Clear;
 for lCntr := 1 to CountOccurancesOf(FldList, ';') + 1 do idxFlds.Add(SubFld(FldList, ';', lCntr));
 if DescWanted and (DescList <> '') then begin
  for lCntr := 1 to CountOccurancesOf(DescList, ';') + 1 do idxDesc.Add(SubFld(DescList, ';', lCntr));
 end;
end;
function FormIndex(idx: TDBISAMIndexDef; IsPrimary: boolean): string;
var
 idxCntr: integer;
begin
 Result := '';
 GetIdxInfo(idx.Fields, idx.DescFields, (ixDescending in idx.Options));
 if idxFlds.Count > 0 then begin
  if not IsPrimary then Result := 'CREATE ';
  if ixCaseInsensitive in idx.Options then Result := Result + ' NOCASE ';
  if IsPrimary then Result := Result + 'PRIMARY KEY ('
  else Result := Result + 'INDEX IF NOT EXISTS "' + idx.Name + '" ON "\Memory\' + tblName + '" (';
  iFlds := dq + idxFlds[0] + dq;
  if (ixDescending in idx.Options)
   and (idxDesc.IndexOf(idxFlds[0]) > -1) then iFlds := iFlds + ' DESC';
  iFlds := iFlds + ',';
  for idxCntr := 1 to idxFlds.Count - 1 do begin
   iFlds := iFlds + dq + idxFlds[idxCntr] + dq;
   if (ixDescending in idx.Options)
    and (idxDesc.IndexOf(idxFlds[idxCntr]) > -1) then iFlds := iFlds + ' DESC';
   iFlds := iFlds + ',';
  end;
  Delete(iFlds, Length(iFlds), 1);
  Result := Result + iFlds + ') ';
  Result := Result + ' COMPRESS NONE ';
  if not IsPrimary then Result := Result + ';';
 end;
end;
begin
idxFlds := TStringList.Create;
idxDesc := TStringList.Create;
if CloneTable <> nil then begin
 tblName := CloneTable.TableName;
 CloneTable.DatabaseName := 'Memory';
 CloneTable.Close;
 CloneTable.Exclusive := True;
end else begin
 tblName := WhichTable.TableName;
 WhichTable.Close;
 WhichTable.Exclusive := True;
end;
Maker := MakeDBISAMQuery('Memory', MakSesh);
Maker.SQL.Clear;
Maker.SQL.Add('DROP TABLE IF EXISTS "\Memory\' + tblName + '";');
Maker.SQL.Add('CREATE TABLE IF NOT EXISTS "\Memory\' + tblName + '"');
Maker.SQL.Add('(');
for Cntr := 0 to WhichTable.FieldList.Count - 1 do begin
 if WhichTable.FieldList[Cntr].FieldKind = fkData
  then Maker.SQL.Add('"' + WhichTable.FieldList[Cntr].FieldName + '" ' + GetFieldType(WhichTable.FieldList[Cntr]) + GetDefaultExpression(WhichTable.FieldList[Cntr]) + ',');
end;
for Cntr := 0 to WhichTable.IndexDefs.Count - 1 do begin
 if ixPrimary in WhichTable.IndexDefs[Cntr].Options then begin
  Maker.SQL.Add(FormIndex(WhichTable.IndexDefs[Cntr], True));
  Break;
 end;
end;
Maker.SQL.Add('LOCALE CODE 0');
Maker.SQL.Add('USER MAJOR VERSION 1');
Maker.SQL.Add(');');
for Cntr := 0 to WhichTable.IndexDefs.Count - 1 do begin
 if not (ixPrimary in WhichTable.IndexDefs[Cntr].Options) then begin
  Maker.SQL.Add(FormIndex(WhichTable.IndexDefs[Cntr], False));
 end;
end;
Maker.ExecSQL;
Maker.Close;
Maker.UnPrepare;
Maker.Free;
idxFlds.Free;
idxDesc.Free;
if CloneTable <> nil then begin
 CloneTable.Exclusive := SetExclusive;
 CloneTable.Active := AndOpen;
end else begin
 WhichTable.Exclusive := SetExclusive;
 WhichTable.Active := AndOpen;
end;
end;

Roy Lambert wrote:
> Allan
>
>
> Something along the lines of
>
> SELECT * INTO "Memory\tbl" FROM DiskTable
> WHERE True = False
>
> The magic is in the WHERE clause which guarantees no data transfered.
>
> Note that this doesn't create any indices.
>
> Personally I created a function (below) to copy the structure.
>
> Roy Lambert
>
>
> procedure SelfMakeMemoryTable(WhichTable, CloneTable: TDBISAMTable; SetExclusive, AndOpen: boolean; const MakSesh: TDBISAMSession);
> var
> Cntr: integer;
> Maker: TDBISAMQuery;
> // MakerSession:TDBISAMSession;
> iFlds: string;
> tblName: string;
> idxFlds: TStringList;
> idxDesc: TStringList;
> const
> dq = '"';
> function GetDefaultExpression(Fld: TField): string;
> begin
>   Result := '';
>   if Fld.DefaultExpression <> '' then begin
>    Result := ' DEFAULT ' + Fld.DefaultExpression;
>   end;
> end;
> function GetFieldType(Fld: TField): string;
> begin
>   case Fld.DataType of
>    ftInteger: Result := 'INTEGER';
>    ftString, ftFixedChar: Result := 'VARCHAR(' + IntToStr(Fld.Size) + ')';
>    ftBoolean: Result := 'BOOLEAN';
>    ftDate: Result := 'DATE';
>    ftFloat: Result := 'FLOAT';
>    ftTime: Result := 'TIME';
>    ftMemo: Result := 'MEMO';
>    ftBlob: Result := 'BLOB';
>    ftGraphic: Result := 'GRAPHIC';
>    ftFmtMemo: Result := 'BLOB(0,3)';
>    ftTypedBinary: Result := 'BLOB(0,7)';
>    ftDateTime: Result := 'TIMESTAMP';
>    ftSmallInt: Result := 'SMALLINT';
>    ftWord: Result := 'WORD';
>    ftAutoInc: Result := 'AUTOINC';
>    ftLargeInt: Result := 'LARGEINT';
>    ftCurrency: Result := 'MONEY';
>    ftBCD: Result := 'DECIMAL(0 , 4)';
>    ftBytes: Result := 'BYTES(' + IntToStr(Fld.Size) + ')';
>   else Result := 'VARCHAR(' + IntToStr(Fld.Size) + ')';
>   end;
> end;
> procedure GetIdxInfo(FldList, DescList: string; DescWanted: boolean);
> var
>   lCntr: integer;
> begin
>   idxFlds.Clear;
>   idxDesc.Clear;
>   for lCntr := 1 to CountOccurancesOf(FldList, ';') + 1 do idxFlds.Add(SubFld(FldList, ';', lCntr));
>   if DescWanted and (DescList <> '') then begin
>    for lCntr := 1 to CountOccurancesOf(DescList, ';') + 1 do idxDesc.Add(SubFld(DescList, ';', lCntr));
>   end;
> end;
> function FormIndex(idx: TDBISAMIndexDef; IsPrimary: boolean): string;
> var
>   idxCntr: integer;
> begin
>   Result := '';
>   GetIdxInfo(idx.Fields, idx.DescFields, (ixDescending in idx.Options));
>   if idxFlds.Count > 0 then begin
>    if not IsPrimary then Result := 'CREATE ';
>    if ixCaseInsensitive in idx.Options then Result := Result + ' NOCASE ';
>    if IsPrimary then Result := Result + 'PRIMARY KEY ('
>    else Result := Result + 'INDEX IF NOT EXISTS "' + idx.Name + '" ON "\Memory\' + tblName + '" (';
>    iFlds := dq + idxFlds[0] + dq;
>    if (ixDescending in idx.Options)
>     and (idxDesc.IndexOf(idxFlds[0]) > -1) then iFlds := iFlds + ' DESC';
>    iFlds := iFlds + ',';
>    for idxCntr := 1 to idxFlds.Count - 1 do begin
>     iFlds := iFlds + dq + idxFlds[idxCntr] + dq;
>     if (ixDescending in idx.Options)
>      and (idxDesc.IndexOf(idxFlds[idxCntr]) > -1) then iFlds := iFlds + ' DESC';
>     iFlds := iFlds + ',';
>    end;
>    Delete(iFlds, Length(iFlds), 1);
>    Result := Result + iFlds + ') ';
>    Result := Result + ' COMPRESS NONE ';
>    if not IsPrimary then Result := Result + ';';
>   end;
> end;
> begin
> idxFlds := TStringList.Create;
> idxDesc := TStringList.Create;
> if CloneTable <> nil then begin
>   tblName := CloneTable.TableName;
>   CloneTable.DatabaseName := 'Memory';
>   CloneTable.Close;
>   CloneTable.Exclusive := True;
> end else begin
>   tblName := WhichTable.TableName;
>   WhichTable.Close;
>   WhichTable.Exclusive := True;
> end;
> Maker := MakeDBISAMQuery('Memory', MakSesh);
> Maker.SQL.Clear;
> Maker.SQL.Add('DROP TABLE IF EXISTS "\Memory\' + tblName + '";');
> Maker.SQL.Add('CREATE TABLE IF NOT EXISTS "\Memory\' + tblName + '"');
> Maker.SQL.Add('(');
> for Cntr := 0 to WhichTable.FieldList.Count - 1 do begin
>   if WhichTable.FieldList[Cntr].FieldKind = fkData
>    then Maker.SQL.Add('"' + WhichTable.FieldList[Cntr].FieldName + '" ' + GetFieldType(WhichTable.FieldList[Cntr]) + GetDefaultExpression(WhichTable.FieldList[Cntr]) + ',');
> end;
> for Cntr := 0 to WhichTable.IndexDefs.Count - 1 do begin
>   if ixPrimary in WhichTable.IndexDefs[Cntr].Options then begin
>    Maker.SQL.Add(FormIndex(WhichTable.IndexDefs[Cntr], True));
>    Break;
>   end;
> end;
> Maker.SQL.Add('LOCALE CODE 0');
> Maker.SQL.Add('USER MAJOR VERSION 1');
> Maker.SQL.Add(');');
> for Cntr := 0 to WhichTable.IndexDefs.Count - 1 do begin
>   if not (ixPrimary in WhichTable.IndexDefs[Cntr].Options) then begin
>    Maker.SQL.Add(FormIndex(WhichTable.IndexDefs[Cntr], False));
>   end;
> end;
> Maker.ExecSQL;
> Maker.Close;
> Maker.UnPrepare;
> Maker.Free;
> idxFlds.Free;
> idxDesc.Free;
> if CloneTable <> nil then begin
>   CloneTable.Exclusive := SetExclusive;
>   CloneTable.Active := AndOpen;
> end else begin
>   WhichTable.Exclusive := SetExclusive;
>   WhichTable.Active := AndOpen;
> end;
> end;
 thank you Roy. Man I gotta say this NG is the best

ab