26 lines
1.2 KiB
MySQL
26 lines
1.2 KiB
MySQL
|
-- Draft p1790621868706_fb5f7cf3 - SuperValu invoice 907260825592619
|
||
|
|
-- 1 of 3: products. The draft has 4 lines but 3 distinct UPCs (rotini is on it twice).
|
||
|
|
-- Values come from the draft; required flags copy the existing rows (dept 1, tax 0, foodstamp 1).
|
||
|
|
-- cost is left at 0, as on file today; script 3 (batch) carries the invoiced cost.
|
||
|
|
USE posbdat;
|
||
|
|
GO
|
||
|
|
|
||
|
|
SET XACT_ABORT ON;
|
||
|
|
BEGIN TRAN;
|
||
|
|
|
||
|
|
INSERT INTO dbo.Products
|
||
|
|
(upc, cert_code, description, cost, normal_price, pricemethod, department, vendor, pack, size,
|
||
|
|
tax, foodstamp, scale, dsd, active, advertised, deleted, attributes, modified, Created, CreatedBy)
|
||
|
|
SELECT v.upc, v.cert_code, v.description, 0, v.normal_price, 0, 1, 1, v.pack, v.size,
|
||
|
|
0, 1, 0, 0, 1, 0, 0, 0, GETDATE(), GETDATE(), 'INVOICE'
|
||
|
|
FROM (VALUES
|
||
|
|
('0002680000550', '645479', 'AMER/BEAUTY ROTINI', 1.39, 1, '16'),
|
||
|
|
('0088810925309', '3491452', 'HST GLAZED HNY BUN', 2.49, 184, NULL),
|
||
|
|
('0019269900079', '4178665', 'SMYNG BLDK ROSE BG BWL 3.', 3.29, 824, '0.65')
|
||
|
|
) AS v(upc, cert_code, description, normal_price, pack, size)
|
||
|
|
WHERE NOT EXISTS (SELECT 1 FROM dbo.Products p WHERE p.upc = v.upc);
|
||
|
|
|
||
|
|
SELECT @@ROWCOUNT AS products_inserted;
|
||
|
|
COMMIT;
|
||
|
|
GO
|