Question:
Is there a way to use an INSERT statement inside of a view? If it is not possible through a view, how do I use an INSERT statement? I need to append data into a productivity table daily and cannot figure out a way to do it in SQL.
My code as of now is:
INSERT INTO Productivity ( USERID, WHSID, DATE, Hours, Stores, SumOfPROQTY, SEC_PC, PC_HR ) SELECT Hours_Q.USERID, Hours_Q.WHSID, Hours_Q.DATE, Hours_Q.Hours, CntStores.CountOfSTORES, Hours_Q.SumOfPROQTY, Hours_Q.SEC_PC, Hours_Q.PC_HR FROM Hours_Q INNER JOIN CntStores ON (Hours_Q.DATE = CntStores.DATE) AND (Hours_Q.USERID = CntStores.USERID)
Answer:
To do inserts into a view, I believe the insert has to reference the PK of one of the base tables driving the view; otherwise, the insert can’t determine which base table (Hours_Q or CntStores) to insert the row into. Put another way, the view has to be restricted to the one table to be updated.
