duplicate error report

I have an app which downloads data from mainframe daily.
Because the primary key definition is different, I have to verify duplicates
before import to my local table.
I would like to know that is it possible to insert duplicates into a table
to investigate later on.
If yes, where is the best place to do it?
For example may I use insert triggers?
I may insert current record into my table if insert fails.
If I am wrong, any information is great appreciated.
Souris,Hi
If you load the data into a loading table then you could do something like:
INSERT INTO Duplicates
SELECT *
FROM LoadingTable
WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
INSERT INTO DestinationTable
SELECT *
FROM LoadingTable
WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
John
"souris" <soukkris@.viddotron.com> wrote in message
news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>I have an app which downloads data from mainframe daily.
> Because the primary key definition is different, I have to verify
> duplicates before import to my local table.
> I would like to know that is it possible to insert duplicates into a table
> to investigate later on.
> If yes, where is the best place to do it?
> For example may I use insert triggers?
> I may insert current record into my table if insert fails.
> If I am wrong, any information is great appreciated.
> Souris,
>
>|||Thanks millions,
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> If you load the data into a loading table then you could do something
> like:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable
> WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
> INSERT INTO DestinationTable
> SELECT *
> FROM LoadingTable
> WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
> John
> "souris" <soukkris@.viddotron.com> wrote in message
> news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Thanks for the sample code.
This works for one primary key column.
Are there any example for 4 primary key columns ?
Thanks again
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> If you load the data into a loading table then you could do something
> like:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable
> WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
> INSERT INTO DestinationTable
> SELECT *
> FROM LoadingTable
> WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
> John
> "souris" <soukkris@.viddotron.com> wrote in message
> news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Hi
Try:
INSERT INTO Duplicates
SELECT *
FROM LoadingTable L
WHERE EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
L.PKCol4 )
INSERT INTO DestinationTable
SELECT *
WHERE NOT EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
L.PKCol4 )
John
souris wrote:
> Thanks for the sample code.
> This works for one primary key column.
> Are there any example for 4 primary key columns ?
> Thanks again
> Souris,
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
something
a|||Thanks millions,
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1108538560.326295.15100@.c13g2000cwb.googlegroups.com...
> Hi
> Try:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable L
> WHERE EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
> L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
> L.PKCol4 )
> INSERT INTO DestinationTable
> SELECT *
> WHERE NOT EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
> L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
> L.PKCol4 )
> John
>
> souris wrote:
> something
> a
>sql