I am trying to dynamically generate SQL INSERT scripts via Excel formulas
My Excel sheet looks as below.
Below is my formula
="INSERT INTO MYTABLE (ROLE_ID,GROUP,SUBGROUP) values('"&D8&"','"&$E$8&"','"&$F$8&"')"
Now, I want the expected output to be generated as below;
So basically, for Column G, I want the generated script to use data from Columns D, E and F. However, the information of Column D would change as per the current row, Columns E and F would repeat till we get an empty row (or as we can use value '0' in Column D to identify that it should now use new values for GROUP and SUBGROUP)
="INSERT INTO SOME_TABLE (MYROLE) values('"&E7&"');"
INSERT INTO SOME_TABLE (MYROLE) values(null); INSERT INTO SOME_TABLE (MYROLE) values('My Role');
=IF(ISBLANK(E7), "INSERT INTO SOME_TABLE (MYROLE) values(null);", "INSERT INTO SOME_TABLE (MYROLE) values('"&E7&"');")
insert into MYSCHEMA.MY_DESTINATION_TABLE(COL_ID, COL2) select 'USER1', COL2 from MYSCHEMA.MY_SOURCE_TABLE where USER_ID = 'USER1';
INSERT INTO MYSCHEMA.MY_DESTINATION_TABLE (COL_ID, COL2) SELECT COL_ID, COL2 FROM MYSCHEMA.MY_SOURCE_TABLE WHERE COL_ID IN (SELECT COL_ID FROM MYSCHEMA.MY_SOURCE_TABLE);
INSERT INTO MYSCHEMA.MY_DESTINATION_TABLE (COL_ID, DESTINATION_COL2) SELECT COL_ID, SOURCE_COL2 FROM MYSCHEMA.MY_SOURCE_TABLE WHERE COL_ID IN (SELECT COL_ID FROM MYSCHEMA.MY_SOURCE_TABLE);
INSERT INTO MYSCHEMA.MY_DESTINATION_TABLE (DEST_COL_ID, DEST_COL2) SELECT SOURCE_COL_ID, CASE WHEN SOURCE_COL1 = 'My Name 1' THEN 'MY_NAME_1' WHEN SOURCE_COL1 = 'My Name 2' THEN 'MY_NAME_2' -- Add more mappings as needed ELSE SOURCE_COL1 -- Default case, if no match is found END FROM MYSCHEMA.MY_SOURCE_TABLE WHERE SOURCE_COL_ID IN (SELECT SOURCE_COL_ID FROM MYSCHEMA.MY_SOURCE_TABLE);
ALTER TABLE TABLE1 ADD CONSTRAINT FK_MYCONSTRRAINT FOREIGN KEY (PERSON_ID, R_ID) REFERENCES MY_ROLE (PERSON_ID, R_ID);
You can use:
=IF(D8:D16=0,"","INSERT INTO MYTABLE (ROLE_ID,GROUP,SUBGROUP) values('"&D8:D16&"','"&E8:E16&"','"&F8:F16&"')")