mardi 4 août 2015

IBM DB2 INSERT when Count(*) = 0

Our development database gets reset weekly. I find myself spending an hour or so on Monday mornings (like today) inserting all of my test data into the various tables and databases from individual scripts.

First, of course, I have to verify that these tables do not have my data:

SELECT Count(*) FROM Table1 WHERE INVLINK LIKE '190701050630%'

If no rows are returned, I can insert my records:

INSERT INTO Table1
(       UPC,    CATALOG, VENDOR,  ADD_DATE, INVLINK, WHO_ADDED, TYPE, VENDOR_ID)
VALUES
('011010017760', 'LOVE', 'TES', '20150609', '19070105063041', 9388, 'P', '1013'),
('011010017760', 'HATE', 'TES', '20150609', '19070105063042', 9388, 'P', '1013'),
('011010017760', 'FEAR', 'TES', '20150609', '19070105063043', 9388, 'P', '1013')

What I would like to know is if there is a syntax that would allow me to call the insert command when the count is zero.

Here is what I've tried, but this will not execute with an SQL call.

SELECT CASE WHEN SELECT Count(*) = 0
THEN 
INSERT INTO TBLBARTRANS
(       UPC,    CATALOG, VENDOR,  ADD_DATE, INVLINK, WHO_ADDED, TYPE, VENDOR_ID)
VALUES
('011010017760', 'LOVE', 'TES', '20150609', '19070105063041', 9388, 'P', '1013'),
('011010017760', 'HATE', 'TES', '20150609', '19070105063042', 9388, 'P', '1013'),
('011010017760', 'FEAR', 'TES', '20150609', '19070105063043', 9388, 'P', '1013')
END
FROM Table1 
WHERE INVLINK LIKE '190701050630%'

I can call the INSERT, SELECT, and UPDATE commands, but I am not a Database Administrator. So, creating a stored procedure to run these 15 to 20 scripts is not an option for me.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire