How to restrict updation in main table while updating view table

See more:

SQL-Server

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;

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')
);

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