Hi

DBISAM 3.30

I want to use the following SQL:

INSERT INTO DestTable
(SKey, SField1, SField2, SField3)
SELECT SKey, SField1, SField2, SField3
FROM SrcTable

However i don't want to add rows for which an SKey already exists in DestTable.  So i gave SKey an unique index, but then the SQL will fail with
a "Duplicate Key" error.

I hoped there would be some way to supress the error for an individual row (using OnPostError) so that the non-duplicates would make it through,
but the event does not even get called.

I realize i can do it using:

INSERT INTO DestTable
(SKey, SField1, SField2, SField3)
SELECT SKey, SField1, SField2, SField3
FROM SrcTable
WHERE SKey NOT IN(SELECT SKey FROM DestTable)

But given that DestTable may contain hundreds of thousands of records that seems an overkill

Dave

INSERT INTO DestTable
(SKey, SField1, SField2, SField3)
SELECT SKey, SField1, SField2, SField3
FROM SrcTable
WHERE SKey NOT IN (SELECT SKey FROM DestTable)

I'm not sure ifi thsi subselct will work in this INSERT statement, but you
can try it.

Regards
Rolf


"Dave" <davey.crackpot@gmail.com> schrieb im Newsbeitrag
news:411242F9-860E-4E10-A415-765898A616A1@news.elevatesoft.com...
> Hi
>
> DBISAM 3.30
>
> I want to use the following SQL:
>
> INSERT INTO DestTable
> (SKey, SField1, SField2, SField3)
> SELECT SKey, SField1, SField2, SField3
> FROM SrcTable
>
> However i don't want to add rows for which an SKey already exists in
> DestTable.  So i gave SKey an unique index, but then the SQL will fail
> with
> a "Duplicate Key" error.
>
> I hoped there would be some way to supress the error for an individual row
> (using OnPostError) so that the non-duplicates would make it through,
> but the event does not even get called.
>
> I realize i can do it using:
>
> INSERT INTO DestTable
> (SKey, SField1, SField2, SField3)
> SELECT SKey, SField1, SField2, SField3
> FROM SrcTable
> WHERE SKey NOT IN(SELECT SKey FROM DestTable)
>
> But given that DestTable may contain hundreds of thousands of records that
> seems an overkill
>
> Dave
>

"Dave" <davey.crackpot@gmail.com> wrote in message
news:411242F9-860E-4E10-A415-765898A616A1@news.elevatesoft.com...
> Hi
>
> DBISAM 3.30
>
> I want to use the following SQL:
>
> INSERT INTO DestTable
> (SKey, SField1, SField2, SField3)
> SELECT SKey, SField1, SField2, SField3
> FROM SrcTable
>
> However i don't want to add rows for which an SKey already exists in
> DestTable.  So i gave SKey an unique index, but then the SQL will fail
> with
> a "Duplicate Key" error.
>

Try this

> INSERT INTO DestTable
> (SKey, SField1, SField2, SField3)
> SELECT SKey, SField1, SField2, SField3
> FROM SrcTable
left outer join desttable on desttable.skey = srctable.skey
where desttable.skey =  null

>
> I realize i can do it using:
>
> INSERT INTO DestTable
> (SKey, SField1, SField2, SField3)
> SELECT SKey, SField1, SField2, SField3
> FROM SrcTable
> WHERE SKey NOT IN(SELECT SKey FROM DestTable)
>
> But given that DestTable may contain hundreds of thousands of records that
> seems an overkill
>

Your SQL would not be any worse than most subqueries, since skey is indexed.
But doing the join one time as in my example should work much faster.

Robert

"Rolf Frei [Team Elevate]" <rolf@eicom.ch> wrote in message
news:F5C8A37A-8674-49C9-B9F5-1D9FEB2AC18A@news.elevatesoft.com...
> INSERT INTO DestTable
> (SKey, SField1, SField2, SField3)
> SELECT SKey, SField1, SField2, SField3
> FROM SrcTable
> WHERE SKey NOT IN (SELECT SKey FROM DestTable)
>
>>
>> I realize i can do it using:
>>
>> INSERT INTO DestTable
>> (SKey, SField1, SField2, SField3)
>> SELECT SKey, SField1, SField2, SField3
>> FROM SrcTable
>> WHERE SKey NOT IN(SELECT SKey FROM DestTable)
>>

Uh?

Robert

> Try this
>
> INSERT INTO DestTable
> (SKey, SField1, SField2, SField3)
> SELECT SKey, SField1, SField2, SField3
> FROM SrcTable
> left outer join desttable on desttable.skey = srctable.skey
> where desttable.skey =  null


Thanks Robert.  Unfortunately I simplified my SQL for my message to make the problem less unweildy and in doing so I removed an existing Left
Outer Join.  So your solution would require a second join which SQL is agin.

d



"Dave" <davey.crackpot@gmail.com> wrote in message
news:411242F9-860E-4E10-A415-765898A616A1@news.elevatesoft.com...
> Hi
>
> DBISAM 3.30
>
> I want to use the following SQL:
>
> INSERT INTO DestTable
> (SKey, SField1, SField2, SField3)
> SELECT SKey, SField1, SField2, SField3
> FROM SrcTable
>
> However i don't want to add rows for which an SKey already exists in
> DestTable.  So i gave SKey an unique index, but then the SQL will fail
> with
> a "Duplicate Key" error.
>



>
> I realize i can do it using:
>
> INSERT INTO DestTable
> (SKey, SField1, SField2, SField3)
> SELECT SKey, SField1, SField2, SField3
> FROM SrcTable
> WHERE SKey NOT IN(SELECT SKey FROM DestTable)
>
> But given that DestTable may contain hundreds of thousands of records that
> seems an overkill
>

Your SQL would not be any worse than most subqueries, since skey is indexed.
But doing the join one time as in my example should work much faster.

Robert

"Dave" <davey.crackpot@gmail.com> wrote in message
news:284DFB3C-4A99-4A43-BB8E-770F735E8516@news.elevatesoft.com...
>> Try this
>>
>> INSERT INTO DestTable
>> (SKey, SField1, SField2, SField3)
>> SELECT SKey, SField1, SField2, SField3
>> FROM SrcTable
>> left outer join desttable on desttable.skey = srctable.skey
>> where desttable.skey =  null
>
>
> Thanks Robert.  Unfortunately I simplified my SQL for my message to make
> the problem less unweildy and in doing so I removed an existing Left
> Outer Join.  So your solution would require a second join which SQL is
> agin.
>

Then use a script. Select the candidates in a separate step

select skey mkey inte memory temp from srctable
left outer join desttable on desttable.skey = srctable.skey
where desttable.skey =  null;
INSERT INTO DestTable
(SKey, SField1, SField2, SField3)
SELECT SKey, SField1, SField2, SField3
FROM SrcTable
join memory temp on mkey = srctable.skey;

The last join will select only the non-duplicates.


Robert

Thanks Robert

I'm afraid i'm having a little difficulty with that SQL.

Recap:

I want to insert into DestTable all records in SourceTable that do NOT exist in CompareTable (while not adding any items that are already in
DestTable)


Here's my original code:

INSERT INTO DestTable
(
sKey,
sField1,
sField2,
sField3
)

SELECT

sKey,
sField1,
sField2,
sField3,

FROM SourceTable S2
LEFT OUTER JOIN CompareTable C2
ON UPPER(S2.sKey) = UPPER(C2.sKey)
WHERE (C2.sKey IS null) AND
(UPPER(S2.sKey) NOT in (Select UPPER(sKey) From DestTable))



Here's my code based on your suggestion:


Select UPPER(sKey) INTO MEMORY TEMP From ...
<SNIP>
;

INSERT INTO DestTable
(
sKey,
sField1,
sField2,
sField3
)

SELECT

sKey,
sField1,
sField2,
sField3,

FROM SourceTable S2
JOIN MEMORY TEMP M2 ON UPPER(M2.Expression)=UPPER(S2.sKey)

(OK I realize I should show you the actual code that i tried, but then the room would fall silent and everybody present would feel rather awkward.  
I'd rather not have that happen again).

Thanks

d

"Dave" <davey.crackpot@gmail.com> wrote in message
news:F1CAC18B-5963-481F-A9AE-994A69C4B0FF@news.elevatesoft.com...
> Thanks Robert
>
> I'm afraid i'm having a little difficulty with that SQL.
>

What I did was to replace the subquery that you have at the end of your SQL
with the following

1. A stand alone SELECT that will create a memory table containing the ids
of the items to be inserted (items that exist in the source table but do not
exist in the destination table)
2. Then simply JOIN this memory table to the update, eliminating the
subquery. This JOIN will filter out any IDs except for the ones present in
the memory table.

Note: since your tables are rather large, do a create index on the memory
table before you do the update. Otherwise, you will have a brute force scan
of the table for every record. Almost as bad as the subquery.

Robert

> Recap:
>
> I want to insert into DestTable all records in SourceTable that do NOT
> exist in CompareTable (while not adding any items that are already in
> DestTable)
>
>
> Here's my original code:
>
> INSERT INTO DestTable
> (
> sKey,
> sField1,
> sField2,
> sField3
> )
>
> SELECT
>
> sKey,
> sField1,
> sField2,
> sField3,
>
> FROM SourceTable S2
> LEFT OUTER JOIN CompareTable C2
> ON UPPER(S2.sKey) = UPPER(C2.sKey)
> WHERE (C2.sKey IS null) AND
> (UPPER(S2.sKey) NOT in (Select UPPER(sKey) From DestTable))
>
>
>
> Here's my code based on your suggestion:
>
>
> Select UPPER(sKey) INTO MEMORY TEMP From ...
> <SNIP>
> ;
>
> INSERT INTO DestTable
> (
> sKey,
> sField1,
> sField2,
> sField3
> )
>
> SELECT
>
> sKey,
> sField1,
> sField2,
> sField3,
>
> FROM SourceTable S2
> JOIN MEMORY TEMP M2 ON UPPER(M2.Expression)=UPPER(S2.sKey)
>
> (OK I realize I should show you the actual code that i tried, but then the
> room would fall silent and everybody present would feel rather awkward.
> I'd rather not have that happen again).
>
> Thanks
>
> d
>

Dave,

Here is one more possibility for you to explore.
I don't know about the performance with big result sets, you must test it.
Maybe using a script as Robert sugested has better performance.

INSERT INTO DestTable  (SKey, SField1, SField2, SField3)
 SELECT SKey, SField1, SField2, SField3 FROM SrcTable
 EXCEPT
 SELECT SKey, SField1, SField2, SField3 FROM DestTable
 ORDER BY SKey

--
Fernando Dias
[Team Elevate]

"Fernando Dias [Team Elevate]" <fernandodias.removthis@easygate.com.pt> wrote:

Dave,

Here is one more possibility for you to explore.
I don't know about the performance with big result sets, you must test it.
Maybe using a script as Robert sugested has better performance.

INSERT INTO DestTable  (SKey, SField1, SField2, SField3)
 SELECT SKey, SField1, SField2, SField3 FROM SrcTable
 EXCEPT
 SELECT SKey, SField1, SField2, SField3 FROM DestTable
 ORDER BY SKey

--
Fernando Dias
[Team Elevate]