insert data in related tables through a view sql server

Question

Hope someone can help with this. I've got two tables (structure below):

Table1
DataYear INT Not Null,
ProvId INT Not Null,
LKId INT Not Null,
UpdateTime Datetime2(2) Not Null,
JTimes INT Not Null

Table2
DataYear INT Not Null,
ProvId INT Not Null,
LKId INT Not Null,
UpdateTime Datetime2(2) Not Null,
IsSuspect BIT,
Recs VARCHAR(10)

The PK's for both tables are the first 4 columns. The view is going to take all the values from Table 1 and the last two columns from table 2.

I want to do an insert data into the view and all columns in both tabels populated. Is this possible? How do I do it?

Hope someone can help

Thanks

Alex

Makes no sense, you insert data into tables, a view then reads them. — Tony Hopkinson, Apr 15 '15 at 10:22
modifying multiple tables in an updatable view is not allowed. refer this http://stackoverflow.com/questions/7281054/sql-updatable-view-with-joined-tables — ughai, Apr 15 '15 at 10:30
You cannot insert to a view where the insert would affect more than one table, in exactly the same way you can't insert to two tables in a single statement. Just an observation though, if the PK for both tables is the same, why do you need two tables? Why not just have one table with the 3 non key columns (JTimes, IsSuspect, Recs)? This way you are not needlessly duplicating the key columns in 2 tables. — GarethD, Apr 15 '15 at 10:37
Thanks for your comments guys... The reason for trying it through was to avoid having to get the application guys to change their code. GarethD - the reason it's two tables is because 10 different feeds will be populating this, but only 2 of them have the extra 2 columns so it was avoid having 2 redundant columns for 80% of the data — denimknight, Apr 15 '15 at 11:13

score 1 · Accepted Answer · answered Apr 15 '15 at 11:20

You can use an INSEAD OF INSERT trigger on the view to insert data into multiple tables.

CREATE TABLE dbo.Table1
    (
      DataYear int NOT NULL
    , ProvId int NOT NULL
    , LKId int NOT NULL
    , UpdateTime datetime2(2) NOT NULL
    , JTimes int NOT NULL
    , CONSTRAINT PK_Table1 PRIMARY KEY(
          DataYear
        , ProvId
        , LKId
        , UpdateTime)
    );

CREATE TABLE dbo.Table2
    (
      DataYear int NOT NULL
    , ProvId int NOT NULL
    , LKId int NOT NULL
    , UpdateTime datetime2(2) NOT NULL
    , IsSuspect bit
    , Recs varchar(10)
    , CONSTRAINT PK_Table2 PRIMARY KEY(
          DataYear
        , ProvId
        , LKId
        , UpdateTime)
    );
GO

CREATE VIEW dbo.View1 AS
SELECT 
      Table1.DataYear
    , Table1.ProvId
    , Table1.LKId
    , Table1.UpdateTime
    , Table1.JTimes
    , Table2.IsSuspect
    , Table2.Recs
FROM dbo.Table1
JOIN dbo.Table2 ON
      Table2.DataYear = Table1.DataYear
    AND Table2.ProvId = Table1.ProvId
    AND Table2.LKId = Table1.LKId
    AND Table2.UpdateTime = Table1.UpdateTime
GO

CREATE TRIGGER INSTEAD_OF_INSERT_View1 
ON dbo.View1 INSTEAD OF INSERT AS
INSERT INTO dbo.Table1(
      DataYear
    , ProvId
    , LKId
    , UpdateTime
    , JTimes)
SELECT 
      DataYear
    , ProvId
    , LKId
    , UpdateTime
    , JTimes
FROM inserted;

INSERT INTO dbo.Table2(
      DataYear
    , ProvId
    , LKId
    , UpdateTime
    , IsSuspect
    , Recs)
SELECT 
      DataYear
    , ProvId
    , LKId
    , UpdateTime
    , IsSuspect
    , Recs
FROM inserted;
GO

INSERT  INTO dbo.View1
        ( DataYear
        , ProvId
        , LKId
        , UpdateTime
        , JTimes
        , IsSuspect
        , Recs
        )
VALUES  ( 1
        , 1
        , 1
        , SYSDATETIME()
        , 1
        , 1
        , '1'
        );
GO

insert data in related tables through a view sql server

1 Answers1