devxlogo

Inserts Inside Views

Inserts Inside Views

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.

See also  How to Create and Deploy QR Codes Online: A Comprehensive Guide
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist