insert into A if B has values that A doesn't

DKY

How do I do an insert values into my table A from table B if B has values in it that table A doesn't have?

bradgrafelman

Here's a rough guess that may or may not work:

INSERT INTO tableA
    SELECT col1, col2 /*, ... -- fill in columns here */
    FROM tableB B
    WHERE B.column_to_compare NOT IN (
        SELECT column_to_compare FROM tableA
    )

DKY

Thank you! Here's what I've got so far and it inserts when I don't have any data in the table but if I add a value to the $VPTbl then run this again it doesn't populate the $MFTbl.

$insertquery = "INSERT INTO ".$MFTbl." (NAV_PN, SOS, UC, DESCRIPTION, VEND, VENDNAME, LEADTIME, TTL_DMD, LINES_LAST_WEEK, SYS_PCNT_FILL, HOME_PCNT_FILL) SELECT ".$VPTbl.".NAV_PN, ".$VPTbl.".SOS, ".$VPTbl.".UC, ".$VPTbl.".DESCR, ".$VPTbl.".VENDOR, ".$VPTbl.".SUPNAME, ".$VPTbl.".LTD, ".$VPTbl.".PR12, ".$VPTbl.".TL, ".$VPTbl.".TPF, ".$VPTbl.".HPF FROM ".$VPTbl." WHERE (".$VPTbl.".NAV_PN NOT IN (SELECT NAV_PN FROM ".$MFTbl.")) AND (MAXXIND = 'X')";

DKY

Any ideas?

sneakyimp

You should try formatting your queries so they are easier to read and you should use table aliasing so you don't have all those ".$VPtb1." bits in there:

INSERT INTO ".$MFTbl." (NAV_PN, SOS, UC, DESCRIPTION, VEND, VENDNAME, LEADTIME, TTL_DMD, LINES_LAST_WEEK, SYS_PCNT_FILL, HOME_PCNT_FILL)
SELECT t.NAV_PN, t.SOS, t.UC, t.DESCR, t.VENDOR, t.SUPNAME, t.LTD, t.PR12, t.TL, t.TPF, t.HPF
FROM ".$VPTbl." t
WHERE (
        t.NAV_PN NOT IN (SELECT NAV_PN FROM ".$MFTbl."))
        AND (t.MAXXIND = 'X')";

Not sure what you mean by "have any data in the table". Which table are you talking about? $VPtbl or $MFTbl?

$VPtbl is your source for this insert/select. If it is empty, then you won't be inserting any records because your SELECT FROM $VPtbl is going to return nothing.