Hi everyone,

I have two tables, with identical structure (almost), each record contains 3
blob fields containing images as well as 30 or so other non-blob fields.
One table is the 'active' table and the other is an 'archive'.  I would like
to be able to move a series of records
from the 'active' table to the 'archive' table and delete them from the
'active' table using SQL.

Is it possible using SQL only ?  The 'archive' table has 1 additional field
(date/time archived) not existing in the 'active' table.

Any advise is appreciated !
BTW, using DBIsam 4.27, Delphi 2007

Thanks
John Taylor

"John Taylor" <jcta@snappysoftware.com> wrote in message
news:4D57C128-07C1-45F5-A506-39B399EE62D4@news.elevatesoft.com...
> Hi everyone,
>
> I have two tables, with identical structure (almost), each record contains
> 3 blob fields containing images as well as 30 or so other non-blob fields.
> One table is the 'active' table and the other is an 'archive'.  I would
> like to be able to move a series of records
> from the 'active' table to the 'archive' table and delete them from the
> 'active' table using SQL.
>
> Is it possible using SQL only ?  The 'archive' table has 1 additional
> field (date/time archived) not existing in the 'active' table.
>

You'll need a script, since you can not combine deletes and inserts on the
same sql statemeent.

insert into archive select *, current_timestamp from active where (active
characteristics);
delete from active where (same where clause as above);

wrap the whole thing in a transaction for speed and safety.

Robert

Thanks Robert.

I'm very challenged when it comes to sql so I'll have to dive into the
learning curve to
figure out how to do this.  Thanks for pointing me in the right direction.

I wasn't so much concerned about doing the delete and insert in the same
operation,
it was handling the blob fields that I was most curious about.

John


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:1A939EC7-199F-4CD3-B69B-AEFDFAF3A4AC@news.elevatesoft.com...
>
> "John Taylor" <jcta@snappysoftware.com> wrote in message
> news:4D57C128-07C1-45F5-A506-39B399EE62D4@news.elevatesoft.com...
>> Hi everyone,
>>
>> I have two tables, with identical structure (almost), each record
>> contains 3 blob fields containing images as well as 30 or so other
>> non-blob fields.
>> One table is the 'active' table and the other is an 'archive'.  I would
>> like to be able to move a series of records
>> from the 'active' table to the 'archive' table and delete them from the
>> 'active' table using SQL.
>>
>> Is it possible using SQL only ?  The 'archive' table has 1 additional
>> field (date/time archived) not existing in the 'active' table.
>>
>
> You'll need a script, since you can not combine deletes and inserts on the
> same sql statemeent.
>
> insert into archive select *, current_timestamp from active where (active
> characteristics);
> delete from active where (same where clause as above);
>
> wrap the whole thing in a transaction for speed and safety.
>
> Robert
>
>

"John Taylor" <jcta@snappysoftware.com> wrote in message
news:CCE3ABFA-3E09-4446-A554-680E464743D9@news.elevatesoft.com...
>
> I wasn't so much concerned about doing the delete and insert in the same
> operation,
> it was handling the blob fields that I was most curious about.
>

You should't have any problems, blobs should be inserted correctly. To be
super safe try the insert portion only first and look at the result table to
make sure it's what you want.

Robert

Robert,

I'm heading in the right direction with the following SQL statement...
INSERT INTO "Archive.sfdata"
(OFT_DATE_SENT, OFT_TIME_SENT, OFT_RECIPIENT,
OFT_TIFBLOB, OFT_LOGBLOB, OFT_RCPBLOB,<other fields>)

SELECT
OFT_DATE_SENT, OFT_TIME_SENT, OFT_RECIPIENT, OFT_TIFBLOB,
OFT_LOGBLOB, OFT_RCPBLOB,<other fields>

FROM "Active.sfdata" WHERE OFT_INDEX IN <subset>

This works !

How can  I update the OFT_DATEARCHIVED field in 'archive' with the current
date in
this same query ?

Is it possible ?

I'm not trying to also delete from 'active' in the same query

Thanks,
John


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:AB2BEA84-4E29-425E-8B70-9E446CB58187@news.elevatesoft.com...
>
> "John Taylor" <jcta@snappysoftware.com> wrote in message
> news:CCE3ABFA-3E09-4446-A554-680E464743D9@news.elevatesoft.com...
>>
>> I wasn't so much concerned about doing the delete and insert in the same
>> operation,
>> it was handling the blob fields that I was most curious about.
>>
>
> You should't have any problems, blobs should be inserted correctly. To be
> super safe try the insert portion only first and look at the result table
> to make sure it's what you want.
>
> Robert
>

John,

<< How can  I update the OFT_DATEARCHIVED field in 'archive' with the
current date in this same query ? >>

Sure, just include the CURRENT_DATE() function in the INSERT statement at
the appropriate position for the OFT_DATEARCHIVED field.

--
Tim Young
Elevate Software
www.elevatesoft.com

Beatiful Tim, thanks.  It works !

Just had a thought... What if the two tables are in different databases
(directories) ?
Which may or may not be the case but highly likely.  What about the
TDBIsamQuery's databasename
property ?  Will I have to take a different approach ?

Sorry for the dumb questions, I'm very weak with SQL

John

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:5335ED6A-DC98-4AB3-89D1-C8715550243C@news.elevatesoft.com...
> John,
>
> << How can  I update the OFT_DATEARCHIVED field in 'archive' with the
> current date in this same query ? >>
>
> Sure, just include the CURRENT_DATE() function in the INSERT statement at
> the appropriate position for the OFT_DATEARCHIVED field.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Got it ... just include the path in the table name.

Thanks for the help, Robert and Tim !


"John Taylor" <jcta@snappysoftware.com> wrote in message
news:C5BFA344-653A-4964-A00B-6F04DFA0FA03@news.elevatesoft.com...
> Beatiful Tim, thanks.  It works !
>
> Just had a thought... What if the two tables are in different databases
> (directories) ?
> Which may or may not be the case but highly likely.  What about the
> TDBIsamQuery's databasename
> property ?  Will I have to take a different approach ?
>
> Sorry for the dumb questions, I'm very weak with SQL
>
> John
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
> news:5335ED6A-DC98-4AB3-89D1-C8715550243C@news.elevatesoft.com...
>> John,
>>
>> << How can  I update the OFT_DATEARCHIVED field in 'archive' with the
>> current date in this same query ? >>
>>
>> Sure, just include the CURRENT_DATE() function in the INSERT statement at
>> the appropriate position for the OFT_DATEARCHIVED field.
>>
>> --
>> Tim Young
>> Elevate Software
>> www.elevatesoft.com
>>
>