如何在更新视图表时限制主表中的更新 [英] How to restrict updation in main table while updating view table

问题描述

我有一个要求，我需要从视图中获取数据并将其插入另一个表但在插入之前我必须检查数据是否存在或不，如果没有，那么只有oi必须插入。



但视图中的数据 - >一列数据将与其他表进行比较并需要使用表数据ID更新该列，它仅用于我的内部目的我不想在视图中更新。



i使用Merge方法进行插入和更新

请检查下面的查询V_CONTRACTS是查看

Hi,

I have a requirement where i need to get data from view and insert it into another table but before inserting i have to check the data is existing or not, if not then only oi have to insert .

but the data in view -> one column data will b compared with other table and need to update that column with table data ID , its only for my internal purpose i dont want to update that in view.

i am using "Merge" method for insertion and updation
please check below query "V_CONTRACTS " is view

--Inserting Suppliername into Suppliers table
merge Suppliers as SC
using V_CONTRACTS as TCon On 
(TCon.SUPPLIER_NAME=SC.SupplierName)
When not matched Then
Insert (SupplierName,EntityID,isdeleted,createdon,lastupdatedon,createdby,lastupdatedby) 
Values (TCon.SUPPLIER_NAME,1,1,getdate(),getdate(),1,1);


--Updating Suppliersname with Suppliersid
MERGE V_CONTRACTS AS target
    USING Suppliers as S On
    (S.SupplierName=target.Supplier_Name)   
    WHEN MATCHED THEN 
     UPDATE SET Supplier_Name = S.ID;

现在这个更新的id我要插入。< br $>

now this updated id i hav to insert .

--Inserting View data into Suppliercontract table
merge TR_ICV_SupplierContract as t
using V_CONTRACTS as src On 
(src.contract_no=t.ContractNumber AND
 src.CONTRACT_DESCRIPTION=t.ContractDescription AND
-- src.CONTRACT_TYPE=t.ContarctType AND 
 src.COMMENCEMENT_DATE = t.PlannedStartDate AND
 src.EXPIRY_DATE = t.Replannedenddate AND
 Src.CONTRACT_HOLDER = t.contractHolder AND
 src.CONTRACT_OWNER = t.contractOwner AND
 src.CONTRACT_ENGINEER = t.contractEngineer AND
 src.TEL_NUMBER = t.phoneNo AND
 src.EMAIL_ADDRESS = t.vendorEmail AND
 src.Supplier_name = t.vendorName
 )
When not matched Then
Insert (ContractNumber,ContractDescription,--ContarctType,
PlannedStartDate,plannedenddate,ReplannedEnddate,contractHolder
,contractOwner,contractEngineer,phoneNo,vendorEmail,vendorName,EntityID,LastupdatedOn,Registrationstatus) 
Values (src.contract_no,
src.CONTRACT_DESCRIPTION,
--src.CONTRACT_TYPE,
src.COMMENCEMENT_DATE,
src.EXPIRY_DATE,
src.EXPIRY_DATE,
Src.CONTRACT_HOLDER,
src.CONTRACT_OWNER,
src.CONTRACT_ENGINEER,
src.TEL_NUMBER,src.EMAIL_ADDRESS,src.Supplier_name,1,Getdate(),(select ID from lookup where Lookup_type = 'Registration Status' and lookup_Value = 'Yet to be Registered')
);

只在这里插入SupplierID - 我在视图中更新id bt我不希望它覆盖。

only to insert SupplierID in this - i am updating id in view bt i dont want it to override .

推荐答案

查看什么都不是他们自己的。数据所有者仍然是用于创建视图的表。当View中的数据更新时，它们将更新表。告诉我们你正在尝试什么，有人可以提供帮助。

View are nothing on their own. The data owner still is the table(s) that was used to create a view. They, table will be updated when data in View is updated. Tell us more on what you are trying and someone can help.

这篇关于如何在更新视图表时限制主表中的更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！