Insert and append records 1

[czampa]

I am new to programming SQL (fixing somebody elses oversights). I am attempting to take data from a source table (updated daily) in insert/update into a destination table which already has 100-200K records. Most of the records are updates, but some will be new records. The logic I used is Update/Set/On/From followed by an IF NOT EXIST INSERT INTO dest table/SELECT/FROM source table.

It appears that the IF NOT EXIST is where my breakdown is occurring. Any thoughts?

 

[mwolf00]

Does this help?

INSERT INTO destTable (val1, val2, val3) SELECT val1, val2, val3 FROM origTabl WHERE val1 NOT IN (SELECT val1 FROM destTable)

 

[czampa]

Possibly, Do I leave the IF NOT EXIST in?

 

[mwolf00]

No, this will insert all of the new records into the table. You will also have to write an update statement that will update records that are already in destTable. I would be clearer if I knew table stucture/primary&foreign keys...

 

[czampa]

Formatting fell apart, but you get the idea. Primary key is cono, prodid, whseid and the sql is as follows:


UPDATE product_warehouse
SET cono = a.cono,
prodid = a.prodid,
whseid = a.whseid,
addoncost = a.addoncost,
arptype = a.arptype,
averagecost = a.averagecost,
baseprice = a.baseprice,
binlocation1 = a.binlocation1,
binlocation2 = a.binlocation2,
prodclass = a.prodclass,
prodfamilygroup = a.prodfamilygroup,
stockunitytd = a.stockunitytd,
lastcost = a.lastcost,
lastinvdate = a.lastinvdate,
lastpowtdate = a.lastpowtdate,
lastrecptdate = a.lastrecptdate,
laststkoutdate = a.laststkoutdate,
aveleadtime = a.aveleadtime,
lastleadtime = a.lastleadtime,
lineptmax = a.lineptmax,
listprice = a.listprice,
nodaysseasonal = a.nodaysseasonal,
nodaysstkout = a.nodaysstkout,
notimesstkout = a.notimesstkout,
ordercalctyp = a.ordercalctyp,
orderpntmin = a.orderpntmin,
orderqtyin = a.orderqtyin,
orderqtyout = a.orderqtyout,
pricetype = a.pricetype,
prodline = a.prodline,
qtybckorder = a.qtybckorder,
qtycommint = a.qtycommint,
qtydemand = a.qtydemand,
qtyintransit = a.qtyintransit,
qtyonhand = a.qtyonhand,
qtyonorder = a.qtyonorder,
qtyrcvd = a.qtyrcvd,
qtyrcvdreqst = a.qtyrcvdreqst,
qtyshipreqst = a.qtyshipreqst,
qtyreservd = a.qtyreservd,
qtyunavail = a.qtyunavail,
ReciptYTD = a.reciptytd,
RebateCost = a.rebatecost,
RebateType = a.rebatetype,
RebateSubType = a.rebatesubtype,
replaccost = a.ReplacCost,
ReplacCostDate = a.replaccostdate,
ReturnsInYTD = a.ReturnsInYTD,
ReturnsOutYTD = a.returnsoutytd,
SftyAlowAmnt = a.sftyalowamnt,
SftyAlowPct = a.sftyalowpct,
SftyAlowType = a.sftyalowtype,
StatusType = a.statustype,
StndCost = a.stndcost,
StndCostDate = a.stndcostdate,
SXUser1 = a.sxuser1,
SXUser2 = a.sxuser2,
SXUser3 = a.sxuser3,
SXUser4 = a.sxuser4,
SXUser5 = a.sxuser5,
SXUser6 = a.sxuser6,
SXUser7 = a.sxuser7,
SXUser8 = a.sxuser8,
SXUser9 = a.sxuser9,
SXUser10 = a.sxuser10,
User1 = a.user1,
User2 = a.user2,
VendId = a.vendid,
UPCNo = a.upcno,
UsageRate = a.usagerate,
UnitBuyMsr = a.unitbuymsr,
VendProd = a.VendProd

FROM GTW_Product_Warehouse AS a LEFT JOIN product_warehouse AS b
ON a.cono = b.cono AND
a.prodid = b.prodid AND
a.whseid = b.whseid

INSERT INTO Product_Warehouse (Cono, AddonCost, AverageCost,
BasePrice, StockUnitYTD, LastCost, LastInvDate, LastPoWtDate,
LastRecptDate, LastStkOutDate, AveLeadTime, LastLeadTime,
LinePtMax, ListPrice, NoDaysSeasonal, NoDaysStkOut,
NoTimesStkOut, OrderPntMin, OrderQtyIn, OrderQtyOut,
QtyBckOrder, QtyCommint, QtyDemand, QtyInTransit, QtyOnHand,
QtyOnOrder, QtyRcvd, QtyRcvdReqst, QtyShipReqst, QtyReservd,
QtyUnavail, ReciptYTD, RebateCost, rebatetype, rebatesubtype, ReplacCost, ReplacCostDate,
ReturnsInYTD, ReturnsOutYTD, SftyAlowAmnt, SftyAlowPct,
StndCost, StndCostDate, UsageRate, ProdId, WhseId, ARPType,
ARPWhseId, BinLocation1, BinLocation2, ProdClass,
ProdFamilyGroup, OrderCalcTyp, PriceType, ProdLine,
SftyAlowType, StatusType, SXUser1, SXUser2, SXUser3,
SXUser4, SXUser5, SXUser6, SXUser7, SXUser8, SXUser9, SXUser10,
User1, User2, VendId, UPCNo, UnitBuyMsr, VendProd)

SELECT cono, addoncost, averagecost, baseprice, stockunitytd, lastcost, lastinvdate, lastpowtdate,
lastrecptdate, laststkoutdate, aveleadtime, lastleadtime,
nodaysseasonal, nodaysstkout, lineptmax, listprice,
notimesstkout, orderpntmin, orderqtyin, orderqtyout,
qtybckorder, qtycommint, qtydemand, qtyintransit, qtyonhand,
qtyonorder, qtyrcvd, qtyrcvdreqst, qtyshipreqst, qtyreservd,
qtyunavail, reciptytd, rebatecost, rebatetype, rebatesubtype, ReplacCost, replaccostdate,
ReturnsInYTD, returnsoutytd, sftyalowamnt, sftyalowpct,
stndcost, stndcostdate, usagerate, prodid, whseid, arptype,
arpwhseid, binlocation1, binlocation2, prodclass,
prodfamilygroup, ordercalctyp, pricetype, prodline,
sftyalowtype, statustype, sxuser1, sxuser2, sxuser3,
sxuser4, sxuser5, sxuser6, sxuser7, sxuser8, sxuser9, sxuser10,
user1, user2, endid, upcno, unitbuymsr, VendProd

From GTW_Product_Warehouse WHERE cono AND whse AND prodid NOT IN (SELECT cono, whse, prodid FROM Product_Warehouse)

 

[mwolf00]

Does this syntax work?
From GTW_Product_Warehouse WHERE cono AND whse AND prodid NOT IN (SELECT cono, whse, prodid FROM Product_Warehouse)

I would have written:
From GTW_Product_Warehouse WHERE cono + '_' + whse + '_' + prodid NOT IN (SELECT cono + '_' + whse + '_' + prodid FROM Product_Warehouse)

Assuming that all three are char or varchar.....

 

[czampa]

My version of that logic did not work, I failed to mention that. I input your suggestions and it shows as being syntactically correct. I will test and reply momentarily.

 

[czampa]

After looking, cono is an integer. How do I format?

 

[mwolf00]

From GTW_Product_Warehouse WHERE cast(cono as varchar(8)) + '_' + whse + '_' + prodid NOT IN (SELECT cast(cono as varchar(8)) + '_' + whse + '_' + prodid FROM Product_Warehouse)
-- What did you expect? This is FREE advice. LOL

 

[czampa]

God blessya, I couldn't get better advice if I paid. Last item I have is a defined foreign key of cono, prodid.

So close!

 

[tlbroadbent]

Use WHERE NOT EXISTS in the following manner.

WHERE NOT EXISTS
(SELECT * FROM Product_Warehouse
WHERE cono=GTW_Product_Warehouse.cono
AND whse=GTW_Product_Warehouse .whse
AND prodid=GTW_Product_Warehouse.prodid)

This will be more efficient and more accurate than attempting to match concatenated columns. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[mwolf00]

Thanks Terry!
I didn't know how to use WHERE NOT EXISTS... You get a star! -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com