DBIsam 4

I have two identical tables, I want to import into tableB, records from
tableA the don't already exist in tableB

The primary index will consist of from 1 to 6 fields.

I know how to use a subselect when the index only consists of 1 field but
how can this be done when the
index consists of more than 1 field ?

I want to use a Query, select the relevant records from tableA, savetostream
and then loadfromstream into tableB

I suppose this would be  join but I'm totally inept at SQL

Help much Appreciated !

JT

"John Taylor" <jcta@snappysoftware.com> wrote in message
news:545C7843-C441-4ED4-A491-8B2DC71E7F16@news.elevatesoft.com...
> DBIsam 4
>
> I have two identical tables, I want to import into tableB, records from
> tableA the don't already exist in tableB
>
> The primary index will consist of from 1 to 6 fields.
>
> I know how to use a subselect when the index only consists of 1 field but
> how can this be done when the
> index consists of more than 1 field ?
>
> I want to use a Query, select the relevant records from tableA,
> savetostream and then loadfromstream into tableB
>
> I suppose this would be  join but I'm totally inept at SQL
>
> Help much Appreciated !
>
Hi JT

I'm half way between DBISAM3 and ElevateDB so this suggestion might not work
....

INSERT INTO TableA
SELECT * FROM TableB
WHERE NOT Field1 IN (SELECT Field1 FROM TableA)
       OR NOT Field2 IN (SELECT Field2 FROM TableA)
       OR NOT Field3 IN (SELECT Field3 FROM TableA)
....

Cheers

Jeff

JT,

I would suggest you look into the EXCEPT clause as that might be easiest
- it relies on actual select columns.

http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=rsdelphi&version=2007&topic=SELECT_Statement

i.e.

select * from tableA
EXCEPT
select * from tableB

and then you get a canned resultset that you should be able to save and
later load.

Raul


On 3/5/2014 3:48 PM, John Taylor wrote:
> DBIsam 4
>
> I have two identical tables, I want to import into tableB, records from
> tableA the don't already exist in tableB
>
> The primary index will consist of from 1 to 6 fields.
>
> I know how to use a subselect when the index only consists of 1 field
> but how can this be done when the
> index consists of more than 1 field ?
>
> I want to use a Query, select the relevant records from tableA,
> savetostream and then loadfromstream into tableB
>
> I suppose this would be  join but I'm totally inept at SQL
>
> Help much Appreciated !
>
> JT

I had my A's and B's reversed - I think

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:7DFE6DD2-A699-481D-843D-63F6488EE31B@news.elevatesoft.com...
>
> INSERT INTO TableA
> SELECT * FROM TableB
> WHERE NOT Field1 IN (SELECT Field1 FROM TableA)
>        OR NOT Field2 IN (SELECT Field2 FROM TableA)
>        OR NOT Field3 IN (SELECT Field3 FROM TableA)
> ...

"Raul" <raul@removethis.raul.ca> wrote in message
news:C95CD579-8FA5-4F29-844A-2509DFED1F3E@news.elevatesoft.com...
> JT,
>
> I would suggest you look into the EXCEPT clause as that might be easiest -
> it relies on actual select columns.
>
> http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=rsdelphi&version=2007&topic=SELECT_Statement
>
> i.e.
>
> select * from tableA
> EXCEPT
> select * from tableB
>
> and then you get a canned resultset that you should be able to save and
> later load.
>
Hi Raul

Much nicer than my suggestion!

I don't have DBISAM4, but presumably that means JT can just do it all in one
statement:-

INSERT INTO tableB
select * from tableA
EXCEPT
select * from tableB

.... which is very cool and I'll have to think of a good excuse to use it
somewhere in my app

Cheers

Jeff

Beautiful Solution Raul !

Thanks to you and Jeff both for helping me out !

JT

"Raul" <raul@removethis.raul.ca> wrote in message
news:C95CD579-8FA5-4F29-844A-2509DFED1F3E@news.elevatesoft.com...
> JT,
>
> I would suggest you look into the EXCEPT clause as that might be easiest -
> it relies on actual select columns.
>
> http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=rsdelphi&version=2007&topic=SELECT_Statement
>
> i.e.
>
> select * from tableA
> EXCEPT
> select * from tableB
>
> and then you get a canned resultset that you should be able to save and
> later load.
>
> Raul
>
>
> On 3/5/2014 3:48 PM, John Taylor wrote:
>> DBIsam 4
>>
>> I have two identical tables, I want to import into tableB, records from
>> tableA the don't already exist in tableB
>>
>> The primary index will consist of from 1 to 6 fields.
>>
>> I know how to use a subselect when the index only consists of 1 field
>> but how can this be done when the
>> index consists of more than 1 field ?
>>
>> I want to use a Query, select the relevant records from tableA,
>> savetostream and then loadfromstream into tableB
>>
>> I suppose this would be  join but I'm totally inept at SQL
>>
>> Help much Appreciated !
>>
>> JT
>

On 3/5/2014 5:36 PM, Jeff Cook wrote:
> I don't have DBISAM4, but presumably that means JT can just do it all in one
> statement:-
>
> INSERT INTO tableB
> select * from tableA
> EXCEPT
> select * from tableB
>
> ... which is very cool and I'll have to think of a good excuse to use it
> somewhere in my app


Yes that would work just fine.

I also like that it's not really dependent on indexes or even table
structures - as long as the select columns match it works.

Raul

This worked on all tables except one table that had blob fields.  The EXCEPT
did not work and I got two different error messages, one referring to ALL is
required blah, blah and the other something about object handle capacity
exceeded.  I should have recorded them as I believe they are issues that
need to be addressed with dbisam.  However, I got that table to work with a
simple sub-query as it only had one field in the index.

JT

"Raul" <raul@removethis.raul.ca> wrote in message
news:EF9514C7-168A-4AFD-AFD7-FBFA9885F468@news.elevatesoft.com...
> On 3/5/2014 5:36 PM, Jeff Cook wrote:
>> I don't have DBISAM4, but presumably that means JT can just do it all in
>> one
>> statement:-
>>
>> INSERT INTO tableB
>> select * from tableA
>> EXCEPT
>> select * from tableB
>>
>> ... which is very cool and I'll have to think of a good excuse to use it
>> somewhere in my app
>
>
> Yes that would work just fine.
>
> I also like that it's not really dependent on indexes or even table
> structures - as long as the select columns match it works.
>
> Raul