So you’ve been given a list of new fees in a Microsoft Excel document that needs to be inserted into your company’s pricing table. All the document contains is the product’s code, it’s new price, and the corresponding state. In the PRICES table in the database the product is not listed as a code, but by it’s unique identifier, and the state is listed by it’s unique identifier, not it’s abbreviation. So how do you resolve to get the missing data and then create INSERT statements to insert the missing data into the actual PRICES table?
Create a temporary table called PRICES_TEMP. It will have five columns:
Next import the data from Excel you have into the appropriate columns, which should leave you with PROD_ID and STA_ID empty. Once the data is imported you could come up with update statements to resolve the PROD_ID and STA_ID. For example, if the PRODUCTS table was where you kept information about the products and it has the product codes stored in a column called PROD_CODE too, you could use the below update statement to update the PROD_ID column correctly.
UPDATE PRICES_TEMP PT
SET PT.PROD_ID = (
SELECT DISTINCT (P.PROD_ID)
FROM PRODUCTS P
WHERE P.PROD_CODE = PT.PROD_CODE);
Likewise you could come up with an update statement to resolve the STA_ID as well. Once that’s completed, you still need to get the data inserted into the PRICES table. The data you have in the temporary table contains the data you need, but it also has extra data that you don’t. So, you can create a SELECT statement like the one below that will generate INSERT statements that you can then run to insert the fees into the PRICES table.
SELECT ‘INSERT INTO PRICES (PROD_ID, STA_ID, FEE) VALUES(‘ || PROD_ID || ‘,’ ||STA_ID || ‘,’ || FEE || ‘);’