Hi Team,
   Roy - Moved my query to here from General..

   Assuming I am at JObNo 1234.

   If I used:
   {sql}
   INSERT INTO TableA
   SELECT * FROM TableA
   WHERE JobNo = 1234
   {sql}
   and JobNo is a Generated field, I suspect this will give me an error as the generated field shouldn't be writeable.
Having said that it is possible that EDB will handle it nicely.

   In would be nice to have a SQL extension that allowed:
   {sql}
   INSERT CURRENT INTO TableA
   {sql}
   that looked after Generated fields.
Regards,
Ian

Hi Team,

   It would also be nice to be able to..
   {sql}
   INSERT INTO "#TemporaryTable" SELECT * FROM "MyTable"
   {sql}
   #TemporaryTable is a temporary table created with the same structure as MyTable.

Regards,
Ian

Ian

>    Assuming I am at JObNo 1234.
>
>    If I used:
>    {sql}
>    INSERT INTO TableA
>    SELECT * FROM TableA
>    WHERE JobNo = 1234
>    {sql}
>    and JobNo is a Generated field, I suspect this will give me an error as the generated field shouldn't be writeable.
>Having said that it is possible that EDB will handle it nicely.

It will give an error

>    In would be nice to have a SQL extension that allowed:
>    {sql}
>    INSERT CURRENT INTO TableA
>    {sql}
>    that looked after Generated fields.

Hmmm, its possible but it would have to be GENERATED ALWAYS only

Is it the time taken to do the processing or is it the problem with typing the query or something else?

I became fed up of this and wrote a simple clone function (below sig) and I might just enhance it to handle keys & generated columns.

Roy

function CloneRecord(tblSource, tblClone: TnlhTable; BestTry: boolean = True): boolean;
var
Cntr: integer;
fNam: string;
begin
Result := True;
tblClone.Open;
tblClone.FieldDefs.Update;
tblSource.FieldDefs.Update;
if tblClone.FieldCount = tblSource.FieldCount then begin
 tblClone.Insert;
 for Cntr := 0 to tblClone.FieldCount - 1 do begin
  fNam := tblClone.FieldDefs[Cntr].Name;
  if tblSource.FieldDefs.IndexOf(fNam) > -1 then begin
   tblClone.FieldByName(fnam).Assign(tblSource.FieldByName(fNam));
  end else begin
   if not BestTry then begin
    Result := False;
    tblClone.Cancel;
    Break;
   end;
  end;
 end;
end else Result := False;
end;

Ian


>    It would also be nice to be able to..
>    {sql}
>    INSERT INTO "#TemporaryTable" SELECT * FROM "MyTable"
>    {sql}
>    #TemporaryTable is a temporary table created with the same structure as MyTable.

You can. If the temporary table is already created then just use it (I generally use in-memory so I'm not sure if you have to precede the table name with TEMPORARY but I don't think so). If the temporary table doesn't exist you can use the CREATE TEMPORARY TABLE ... WITH DATA construct.

Roy

Roy Lambert wrote:

>Is it the time taken to do the processing or is it the problem with typing the query or something else?

Time.

> I became fed up of this and wrote a simple clone function (below sig) and I might just enhance it to handle keys &
> generated columns.

Feel free.

Ian

I don't know how useful this is in your case, but a while ago, Tim gave
me the code below my sig.

This function returns the SQL to create a file (Temporary or otherwise)
that has the same structure as another table or query.

Cheers

Jeff
=============================================================
function GetCreateTableStatement(DataSet: TEDBDataSet; const TableName:
String;
  TemporaryTable: Boolean = True): String;
var
  TempTableName: String;
  TempSQL: TStrings;
  i: Integer;
  TempFieldName: String;
  TempFieldSQL: String;
begin
  TempSQL := TStringList.Create;
  try
    TempTableName := CheckIdentifier(TableName);
    with DataSet do
    begin
      Open;
      FieldDefs.Update;
      if TemporaryTable then
        TempSQL.Add('CREATE TEMPORARY TABLE "' + TempTableName + '"')
      else
        TempSQL.Add('CREATE TABLE "' + TempTableName + '"');
      TempSQL.Add('(');
      for i := 0 to FieldDefs.Count - 1 do
      begin
        TempFieldName := CheckIdentifier(FieldDefs[i].Name);
        TempFieldSQL := '"' + TempFieldName + '"' + ' ' +
          UpperCaseString(FieldTypeToSQL(FieldDefs[i].DataType,
          FieldDefs[i].Size));
{$IFDEF D10ORHIGHER}
        if (FieldDefs[i].DataType in [ftWideString, ftFixedWideChar,
          ftWideString, ftFixedChar, ftGUID, ftWideMemo, ftMemo]) then
{$ELSE}
        if (FieldDefs[i].DataType in [ftWideString, ftWideString,
ftFixedChar,
          ftGUID, ftMemo]) then
{$ENDIF}
          TempFieldSQL := TempFieldSQL + ' COLLATE ' +
            GetCollationForField(FieldDefs[i].Name);
        if (i < (FieldDefs.Count - 1)) then
          TempFieldSQL := TempFieldSQL + ',';
        TempSQL.Add(TempFieldSQL);
      end;
      TempSQL.Add(')');
      Result := TempSQL.Text;
    end;
  finally
    FreeAndNil(TempSQL);
  end;
end;

Ian,

<<   It would also be nice to be able to..
   {sql}
   INSERT INTO "#TemporaryTable" SELECT * FROM "MyTable"
   {sql}
   #TemporaryTable is a temporary table created with the same structure as MyTable. >>

You can do that:

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=INSERT

Tim Young
Elevate Software
www.elevatesoft.com

Ian,

<< and JobNo is a Generated field, I suspect this will give me an error as the generated field shouldn't be writeable.
Having said that it is possible that EDB will handle it nicely. >>

EDB will allow you to do this with SQL - it doesn't prevent you from overwriting generated columns because EDB will simply overwrite whatever you set with the generated value during the INSERT.  In the case of computed columns, EDB simply ignores any attempts to assign a value to a computed column.

It is only the navigational access that is blocked from directly modifying computed/generated columns.

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Ian,
>
> <<   It would also be nice to be able to..
>    {sql}
>    INSERT INTO "#TemporaryTable" SELECT * FROM "MyTable"
>    {sql}
>    #TemporaryTable is a temporary table created with the same structure as MyTable. >>
>
> You can do that:
>
> https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=INSERT
>
> Tim Young
> Elevate Software
> www.elevatesoft.com

Hi Tim,
   Ahh. Yes. But, unless I am missunderstanding, currently, the temporary table has to have already been created to
insert into,  I was hoping to have it automatically be created based on the insert and source table.

Regards,
Ian

Tim


That's neat - another thing to add into my internal repository

Roy Lambert