CODEWITHSUNDEEP
sql-serverviewinsert-update
denimknight
denimknight

Reputation: 321

insert data in related tables through a view sql server

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

Upvotes: 0

Views: 54

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

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

Upvotes: 1

Related Questions