Not sure if this works in all databases, but I just tried it in ASA8 and it's fine. It should work in all databases as it's just a slightly different way of inserting rows.

insert into tblname (col1, col2, row_key, blobdata)
  select col1, col2, :new_row_key, blobdata 
  from tblname
  ​where row_key = :old_row_key
using sqlca;
// add code to check sqlca for success here
commit using sqlca;

Basically, let the database engine do all the work of duplicating the actual data, and not bring it back to PB at all.

Thanks for the ideas.

I thought about it some more and I think I will probably be changing the primary key so that I don't need to have multiple copies. Poor database design by 2005 me.

i believe that is the fastest way, other than writing multiple stored procedures.

The size of the blob matters too - so you may want to check the size of the blob before retrieval if you have any larger blobs and then retrieve/update chunks at a time.

the select and update for blob are the same across all 4 databases, but getting the size is different in oracle:  (datalength vs dbms_lob.getlength).

if you use odbc, then you may have size limitations even smaller (8K) than other connection types (at least on sql server) .

Hi Roland,

I suppose you wouldn't want to use a stored procedure, which would be the fastest way?

I'm afraid that for the Blob fields you would have to do a selectblob + updateblob for every row then ... (unless the blobs have little data, I think less than 32Kb, then you could just use the datawindow, depending also of your database driver).

For duplication of the rest of the rows their data (before the blob stuff), I'd think of retrieving the data with a atastore / dw  and then copy the "Data" property of that retrieved dw to another, set the SetItemStatus to newModified! (make sure the primary keys are ok, or have a trigger that takes care of it).

Hope it gives you an idea.