Re: Load duplicates

--000000000000a9727c0602fa6ec4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

So are you reading the records one at a time through DBI and looking for
duplicates? There is probably a better way. =F0=9F=98=81

I work with Teradata, so I'll use Teradata syntax, but the syntax for other
databases will be similar.

To find the duplicates in your table, you can select all columns from the
table (except the auto-increment) along with a count, like so:

create volatile table ##dup_counts
as
(select acct, comp, accr, descrip, all-other-columns...,
min(auto_increment) min_num /* or max() */ , count(*) kount
from table1
group by acct, comp, accr, descrip, all-other-columns... )
with data;

Now there are no duplicates in ##dup_counts, so you can insert them to your
target table, either generating a new auto_increment or using min_num.

The duplicates can now be inserted into your error table:
insert into error_table
select acct, comp, ...etc.
from ##dup_counts
where kount > 1;

Et voil=C3=A0 ... the records are deduped with only three statements.

As you probably know, in relational databases rows do not have an order, so
I'm not sure that you necessarily need a sequence number on the duplicates.
If you want to trace the duplicates back to a flat file source, it might be
better to add a sequence number to the file before loading it to the
database.

I hope this helps.


On Tue, Aug 15, 2023 at 11:50=E2=80=AFAM Ian <[email protected]> wrote:

> Hi group,
>
> My perl skills are basic and my SQL skills almost match that.
> Using perl 5.28 and mysql on windows.
>
> I have a couple million records that needs processing to go to their fina=
l
> destination.
> Currently I'm catchin duplicates with error 1062 in perl and using that t=
o
> write the duplicate records to a separate table for later processing.
>
> Question: Can DBI give me a field from the record on the main table
> causing the duplicate so I can add that to the record going to
> the duplicate table?
>
> Example:
> MAIN table fields =3D "auto_increment", acct, comp,accr,desc,etc....  :
>  (auto_increment created when records are added, rest is the source recor=
d)
> Duplicate table fields =3D "auto_increment from MAIN table",
> acct,comp,accr,desc,etc.....
>
> Thanks
> Ian
>
>
>

--000000000000a9727c0602fa6ec4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>So are you reading the records one at a time through =
DBI and looking for duplicates? There is probably a better way. =F0=9F=98=
=81</div><div><br></div><div>I work with Teradata, so I&#39;ll use Teradata=
 syntax, but the syntax for other databases will be similar.</div><div><br>=
</div><div>To find the duplicates in your table, you can select all columns=
 from the table (except the auto-increment) along with a count, like so:</d=
iv><div><br></div><div>create volatile table ##dup_counts</div><div>as</div=
><div>(select acct, comp, accr, descrip, all-other-columns..., min(auto_inc=
rement) min_num /* or max() */ , count(*) kount</div><div>from table1</div>=
<div>group by=20
acct, comp, accr, descrip, all-other-columns... )</div><div>with data;</div=
><div><br></div><div>Now there are no duplicates in ##dup_counts, so you ca=
n insert them to your target table, either generating a new auto_increment =
or using min_num.</div><div><br></div><div>The duplicates can now be insert=
ed into your error table:</div><div>insert into error_table</div><div>selec=
t acct, comp, ...etc.</div><div>from ##dup_counts</div><div>where kount &gt=
; 1;</div><div><br></div><div>Et voil=C3=A0 ... the records are deduped wit=
h only three statements.</div><div><br></div><div>As you probably know, in =
relational databases rows do not have an order, so I&#39;m not sure that yo=
u necessarily need a sequence number on the duplicates. If you want to trac=
e the duplicates back to a flat file source, it might be better to add a se=
quence number to the file before loading it to the database.</div><div><br>=
</div><div>I hope this helps.</div><br></div><br><div class=3D"gmail_quote"=
><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Aug 15, 2023 at 11:50=E2=80=
=AFAM Ian &lt;<a href=3D"mailto:[email protected]">[email protected]</a>&gt; =
wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr">Hi group,<div><br></div><div>My perl skills are basic and my SQL s=
kills almost match that.</div><div>Using perl 5.28 and mysql on windows.</d=
iv><div><br></div><div>I have a couple million records that needs processin=
g to go to their final destination.</div><div>Currently I&#39;m catchin dup=
licates with error 1062 in perl and using that to write the duplicate recor=
ds to a separate=C2=A0table for later processing.<br></div><div><br></div><=
div>Question: Can DBI give me a field from the record on the main table cau=
sing the duplicate so I can add=C2=A0that to the record going to the=C2=A0d=
uplicate table?</div><div><br></div><div>Example:</div><div>MAIN table fiel=
ds =3D &quot;auto_increment&quot;, acct, comp,accr,desc,etc....=C2=A0 :</di=
v><div>=C2=A0(auto_increment created when records are added, rest is the so=
urce record)</div><div>Duplicate table fields =3D &quot;auto_increment from=
 MAIN table&quot;, acct,comp,accr,desc,etc.....</div><div><br></div><div>Th=
anks</div><div>Ian</div><div><br></div><div><br></div></div>
</blockquote></div>

--000000000000a9727c0602fa6ec4--