Hi Team,
   I have a copy routine that reads the individual fields of the current record, appends a new record, then writes the
old values to the new record.  As the first field is an autoinc/generated field it ignores it.
   It works fine but is a little slow.
   Are there any alternatives hidden in EDB anywhere that might be quicker?
Regards & TIA,
Ian

Ian


I think I know what you're talking about but I'm not exactly sure. Going with my guess:

INSERT INTO table
SELECT comma seperated list of fields EXCLUDING the primary id FROM table WHERE idfield = idtocopy




Roy Lambert

Hi Roy,
   See.  I was so wrapped up in thinking about the code solution I din't think about a SQL solution...
   Old habits die hard. ;-(

Regards,
Ian

> INSERT INTO table
> SELECT comma seperated list of fields EXCLUDING the primary id FROM table WHERE idfield = idtocopy
>
Hi Roy,
   Can't find any reference to the 'EXCLUDING' qualifier.

Regards,
Ian

Ian


>    Can't find any reference to the 'EXCLUDING' qualifier.

That's an instruction to you not the sql tokeniser

Roy

>
> That's an instruction to you not the sql tokeniser
>
Ahh, sorry, the Capitals got me.

Cheers.

If your Primary Key field is "ID"

WHERE NOT ID = :SomeParam

If this is a process you are going to use regularly you can write a stored-procedure to automate it. All you need to include as parameters are the name of the table to be duplicated, and the value of the primary key of the duplicate.

Within the procedure you can iterate the field-names using the Configuration tables to generate the insert.

Here is a procedure that will do this, for simple data-tables where field-names do not contain spaces, and where the schema has a built in assumption that ALL primary key fields are an integer called "ID":

CREATE PROCEDURE "DuplicateRecord" (IN "TableName" VARCHAR COLLATE "ANSI", IN "ID" INTEGER)
BEGIN
 DECLARE Crsr CURSOR FOR Stmt;
 DECLARE FieldNames CLOB;
PREPARE Stmt FROM
' SELECT
 LIST(Name) as FieldNames
 FROM Information.TableColumns
 WHERE TableName = ?
 AND NOT FieldName = ''ID'' ';
OPEN Crsr Using TableName;
FETCH FIRST FROM Crsr('FieldNames') INTO FieldNames;
EXECUTE IMMEDIATE
' INSERT INTO "' + TableName + '"
 (' + FieldNames + ')
  SELECT '
    + FieldNames +
' FROM "' + TableName + '"
  WHERE ID = ' + CAST(ID as VARCHAR);
END

Ian


If you're prepared to move from GENERATED ALWAYS to GENERATED BY DEFAULT there's a nifty way:

table.CopyOnAppend := True;

table.insert;

tablePRIMARYKEYFIELDS.Clear <<<<<<<<<<<<<<<<<<<<<<< you have to have one line per field - to avoid misunderstanding PRIMARYKEYFIELDS is not built in its just my notation

table.post;

table.CopyOnAppend := False;

I'm assuming the table cursor is positioned on the record you want to duplicate.


CopyOnAppend is also available in EDBManager

Roy Lambert

Hi Roy,
   Now that is nifty.
   Harking back to your eralier response on my Always v By Default, you indicated the if the field is set to By Default
it can be edited.  That would be as in edited like any other field?  If I set the field to Read Only, or at least any
components that point to it, that should mitigate the risk?
   I note the other three tables that migrated over with AutoInc fields are Generated By Default and there dowsn't seem
to be any issues att.

Regards,
Ian

Think I'd be inclined to wrap it in a Transaction..