[MSSQL] voorkomen insert duplicate key / subquery probleem

[Pelle]

De situatie is als volgt:


table1:

partnr	company_id	price
aap	c1	50
aap	c2	48
noot	c1	10

table2:

product_id	partnr
1	aap
2	noot

table3:

product_id	company_id	price
1	c1	50
2	c1	10
1	c2	48

Voor elk record in table1 moet er een record in table3 geinsert óf geupdate worden.
Table2 fungeert hier als koppeltabel tussen table1 en table3; een partnummer is gekoppeld aan een product_id, en betreffende product_id moet dus in table3 komen.
Indien er al een combinatie van product_id & company_id in table3 voorkomt, dan moet er een update van de price plaatsvinden. In table3 zijn product_id en company_id samen de PK.

Ik dacht dit in 2 stappen op te gaan lossen, eerst een insert en daarna een update. Ik kom echter in de knoop met de company_id's.
Stel, ik wil alleen product_id en price inserten in table3 (het veld company_id negeer ik hier dan even), dan doe ik dat zo:

code:

INSERT INTO table3 (product_id, price)
SELECT      table2.product_id,
            table1.price
FROM        table1
INNER JOIN  table2
ON          table1.partnr       = table2.partnr
WHERE       table2.product_id   NOT IN 
                              ( SELECT product_id
                                FROM table3 )

Oftewel, selecteer alle records uit table2 waarvan het product_id nog niet voorkomt in table3, koppel die aan de prijs uit table1 en insert die records in table3.
Echter, het probleem is zoals gezegd die company_id: hoe kan ik de unieke combinatie van product_id en company_id in één (of meerdere) subquery's vangen, zodat ik geen dubbele combinaties zal inserten? Door de PK-constraint krijg ik (terecht) een foutmelding zodra ik een duplicate key probeer te inserten, en dat wil ik dus niet
Zit me er nou al een tijd blind op te staren, maar het schiet niet echt op... zoiets zou het moeten worden, maar dan op de plek van de comments een hele mooie WHERE-clause die dubbele inserts voorkomt.

code:

INSERT INTO table3 (product_id, company_id, price)
SELECT      table2.product_id,
            table1.company_id,
            table1.price
FROM        table1
INNER JOIN  table2
ON          table1.partnr       = table2.partnr
WHERE   
        /*  en dan hier een subquery of clausule 
            die alle reeds bestaande combinaties
            van product_id en company_id ophaalt
            o.i.d. */

Het mooiste zou zijn als de stored procedure waarmee ik dit verhaal wil uitvoeren, gewoon duplicate-key inserts negeert (en dus niet stopt met executen als zoiets
zich voor doet, maar gewoon verder gaat met het volgende record), maar ik weet niet of dat mogelijk is.

Misschien zit ik wel helemaal op een verkeerd spoor; dus als iemand hier een beter idee over heeft, graag

[curry684]

Zoiets?

SQL:

BEGIN TRANSACTION

UPDATE table3
SET price = t1.price 
FROM table1 t1 
JOIN table2 t2 ON t1.partnr = t2.partnr
WHERE table3.product_id = t2.product_id AND table3.company_id = t1.company_id;

INSERT INTO table3 (product_id, company_id, price)
SELECT t2.product_id, t1.company_id, t1.price
FROM table1 t1
INNER JOIN table2 t2 ON t1.partnr = t2.partnr
WHERE NOT EXISTS 
   (SELECT 1
    FROM table3 ti3 
    WHERE ti3.product_id = t2.product_id AND ti3.company_id = t1.company_id);

COMMIT TRANSACTION

SELECT * FROM table3;

Dit lijkt hier iig perfectemundo te werken

btw. ik doe de update express eerst, omdat deze anders potentieel dubbel werk verricht op de rows die net geinsert zijn

[ Voor 11% gewijzigd door curry684 op 27-09-2004 13:31 ]

[Verwijderd]

Ik snap niet wat je wilt proberen. Corrigeer me svp als ik het verkeerd zie. Volgens mij is Table2 geen koppeltabel maar is Table3 een berekend resultaat uit table 1 en 2. Dit denk ik onder andere omdat zowel tabel 1 als 3 een kolom prijs bevatten.

Ik zou het zo doen:

code:

TRUNCATE TABLE3;

INSERT INTO TABLE3
SELECT product_id, company_id, price
FROM Table1, Table2
WHERE Table1.partnr = Table2.partnr

[curry684]

Ja het is een computed total maar als hier 100000 produkten instaan wil je niet iedere keer een trunc doen en de tabel overnieuw aanmaken, dat kan met indexen, page splits et al minuten of uren duren. Puur updaten wat al bestaat is tig keer sneller.

[whoami]

curry684 schreef op 28 september 2004 @ 15:04:
Ja het is een computed total maar als hier 100000 produkten instaan wil je niet iedere keer een trunc doen en de tabel overnieuw aanmaken, dat kan met indexen, page splits et al minuten of uren duren. Puur updaten wat al bestaat is tig keer sneller.

Soms wil je dat ook ivm historische data. Je wilt bv weten hoeveel de klant betaald heeft voor product A. Je wilt dus de prijs hebben die op dat moment geldig was, en niet de prijs die nu geldig is.

[Verwijderd]

curry684 schreef op 28 september 2004 @ 15:04:
Ja het is een computed total maar als hier 100000 produkten instaan wil je niet iedere keer een trunc doen en de tabel overnieuw aanmaken, dat kan met indexen, page splits et al minuten of uren duren. Puur updaten wat al bestaat is tig keer sneller.

Updaten is beter als de huidige inhoud van Table3 moet blijven staan, bv omdat er afhankelijke gegevens zijn die er bij een verwijderactie ook aan gaan.
Als het alleen op reproductie van Table3 aankomt weet ik niet wat sneller is: 100.000+ records regenereren gaat best wel bruut snel. Hoe snel een benadering met inserts en updates werkt hangt af van hoeveel % er upgedate zou moeten worden. Meten is weten .