Click here to Skip to main content
Click here to Skip to main content

How to insert data using SQL Views created using multiple tables

, 5 Aug 2011
Rate this:
Please Sign up or sign in to vote.
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. Co

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.

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.


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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
I have been in software industry for more than 8 years. I have developed different type of software using different languages. Many of them are database related (both web & window based), SQL being as the back end most of the time. Up-to-date I have knowledge in languages such as C#, VB.Net, T-SQL, JAVA, VB6 & C++, making C# the most proficient of all. Also I have worked using different technologies like ASP.Net, SharePoint, Crystal Reports (But I really hate designing reports) & MS SQL Server and have involved in designing & developing software for major companies like FedEx, Softlogic Holdings, IronOne Technologies & Brandix. Currently I am working as a Tech Lead in Singapore.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinpremiumOmar Gameel Salem8-May-14 14:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 5 Aug 2011
Article Copyright 2011 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid