Friday 5 August 2011

How to insert data using SQL Views created using multiple tables

A view can be defined as a virtual table or a stored query and the data accessible through a view is not stored in the database as a distinct object. Only the select statement is stored on the database instead.

How ever views can be used and perform DML operations (Insert, Update & Delete) also.

Consider the following two tables.

CREATE TABLE STUDENT(
STD_ID INT,
STD_FNAME VARCHAR(20),
STD_LNAME VARCHAR(20)
)


CREATE TABLE STUDENT_PAYMENT(
STD_ID INT,
PAY_AMT MONEY,
PAY_DATE DATETIME
)



Now create the following views.


CREATE VIEW VW_STUDENT
AS
SELECT
STD_ID,
STD_FNAME,
STD_LNAME
FROM
STUDENT


CREATE VIEW VW_STUDENT_PAYMENT
AS
SELECT
STD_ID,
PAY_AMT,
PAY_DATE
FROM
STUDENT_PAYMENT


You can insert data to the above tables using the views we have just created. And it is the same syntax that we use to insert data to tables.


INSERT INTO VW_STUDENT
SELECT 1,'Peter','Parker' UNION
SELECT 2,'James', 'Watson'


INSERT INTO VW_STUDENT_PAYMENT
SELECT 1,1000,'01/01/2011' UNION
SELECT 1,1100,'01/02/2011' UNION
SELECT 1,1200,'01/03/2011' UNION
SELECT 1,1250,'01/04/2011' UNION
SELECT 1,1375,'01/05/2011' UNION
SELECT 2,750,'01/03/2011' UNION
SELECT 2,850,'01/04/2011' UNION
SELECT 2,950,'01/05/2011'

And if you query the tables you can see that the records have inserted correctly.

img_scr_009


Now we will create the following view. This time we will join two tables and create a somewhat complex query.


CREATE VIEW VW_LAST_PAYMENT_DETAILS AS
WITH CTE_STD (STD_ID,MAX_PAYDATE) AS (
SELECT SP.STD_ID, MAX(SP.PAY_DATE) AS MAX_PAYDATE
FROM STUDENT_PAYMENT AS SP
GROUP BY SP.STD_ID
)
SELECT S.STD_ID,S.STD_FNAME,S.STD_LNAME, P.PAY_AMT,P.PAY_DATE
FROM STUDENT AS S
JOIN STUDENT_PAYMENT AS P ON S.STD_ID = P.STD_ID
JOIN CTE_STD AS Q ON P.STD_ID = Q.STD_ID AND P.PAY_DATE = Q.MAX_PAYDATE
GROUP BY S.STD_ID,S.STD_FNAME,S.STD_LNAME, P.PAY_AMT,P.PAY_DATE


Using the above created view we can list the last payment details of each student.


img_scr_004


So if we required to insert last payment details using this view how shall we do it ? If you use the simple insert statements similar to the ones, we used earlier, you have could ended up with the following error.


INSERT INTO VW_LAST_PAYMENT_DETAILS (STD_ID,PAY_AMT,PAY_DATE)
SELECT 1,4440,GETDATE()


img_scr_007


In order to insert (update & delete) data to views created using multiple tables, you need to use an ‘Instead of trigger’.


**Please note that ‘After Triggers’ cannot be created for views.


Let’s create an instead of trigger using the following syntax.


CREATE TRIGGER TRGI_VW_PAYMENT ON VW_LAST_PAYMENT_DETAILS
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO STUDENT_PAYMENT
SELECT STD_ID,PAY_AMT,PAY_DATE
FROM INSERTED
END


Now using the above insert syntax, you can insert data without getting any error. If you inspect the ‘STUDENT_PAYMENT’ table you can see that the data  has been inserted successfully.


img_scr_010

6 comments:

  1. Great post. I would like learn more by following your blog posts. Please know as well that I sent you a direct e-mail. Hope you respond to it soon. Thanks.

    ReplyDelete
  2. Nice post. It really helped.

    ReplyDelete
  3. nice post...it really superb..keep post like this
    always..

    ReplyDelete
  4. Replies
    1. INSERTED & DELETED tables are two tables which is available inside a trigger or during MERGE operation. These are also called "Magic Tables" INSERTED will contain the new data during an INSERT or changed data during an UPDATE. DELETED will contain deleted data in a DELETE or changed data during an UPDATE, in side the trigger.

      Delete