Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 11 total |
Append a copy of a record?? |
Wed, Jun 27 2018 7:00 AM | Permanent Link |
Ian Branch | 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 |
Wed, Jun 27 2018 7:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Jun 27 2018 4:37 PM | Permanent Link |
Ian Branch | 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 |
Wed, Jun 27 2018 7:53 PM | Permanent Link |
Ian Branch | > 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 |
Thu, Jun 28 2018 1:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
> Can't find any reference to the 'EXCLUDING' qualifier. That's an instruction to you not the sql tokeniser Roy |
Thu, Jun 28 2018 2:56 AM | Permanent Link |
Ian Branch | >
> That's an instruction to you not the sql tokeniser > Ahh, sorry, the Capitals got me. Cheers. |
Thu, Jun 28 2018 4:48 AM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Fri, Jun 29 2018 2:22 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Jun 29 2018 4:35 AM | Permanent Link |
Ian Branch | 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 |
Fri, Jun 29 2018 4:49 AM | Permanent Link |
Ian Branch | Think I'd be inclined to wrap it in a Transaction..
|
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |