I have a query to insert data from source to destination table in sql server.Records from source table should be inserted into destination table if they are not present in destination table and if records are present they should be updated.
Source Table is [DistributorPortal].cdgmaster.Customer
Destination Table is cdgmaster.Customer
I have written the following procedure
ALTER PROCEDURE [dbo].[Distributor_Portal_Lakshaya_Source_Destination_Update]
AS
BEGIN
Begin Try
Declare @sourcerecords int;
Declare @destinationrecords int;
Select @destinationrecords = Count(*) from [DistributorPortal].cdgmaster.Customer where CustomerCode in (Select CustomerCode from cdgmaster.Customer)
Select @sourcerecords = Count(CustomerCode) from cdgmaster.Customer
If @destinationrecords < @sourcerecords
Begin
Insert into [DistributorPortal].cdgmaster.Customer (CustomerCode,CustomerName,CustomerAddress1,CustomerAddress2,CustomerAddress3,CustomerID,SAPID,RegionCode,ZoneCode,TerritoryCode,StateCode,TownCode,TypeCode,PSnonPS,EmailID,MobileLL,SuppliedBy,DrugLicense,CurrentYTD,DSByChannelCode,DSByStrategicCode,ClassificationCode,IsActive,DeactiveDt,ActiveDt,AnnualIncentivePlan,isTarangUmang,WholeSalerCode,isIME,createdBy,NKACStores,ParentCustomerCode,IsDirectAcct,ABICode,DistributorCode,DistributorSAPID,SAPZoneCode,SAPTerritoryCode,IsConfirm,NkacRegion,NkacZone,NkacTerritory,Channel,GLN,TransitDays,TransitHours,VMRApply) Select CustomerCode,CustomerName,CustomerAddress1,CustomerAddress2,CustomerAddress3,CustomerID,SAPID,RegionCode,ZoneCode,TerritoryCode,StateCode,TownCode,TypeCode,PSnonPS,EmailID,MobileLL,SuppliedBy,DrugLicense,CurrentYTD,DSByChannelCode,DSByStrategicCode,ClassificationCode,IsActive,DeactiveDt,ActiveDt,AnnualIncentivePlan,isTarangUmang,WholeSalerCode,isIME,createdBy,NKACStores,ParentCustomerCode,IsDirectAcct,ABICode,DistributorCode,DistributorSAPID,SAPZoneCode,SAPTerritoryCode,IsConfirm,NkacRegion,NkacZone,NkacTerritory,Channel,GLN,TransitDays,TransitHours,VMRApply from cdgmaster.Customer where CustomerCode not in(Select CustomerCode from cdgmaster.Customer)
Update d Set d.CustomerName = c.CustomerName,d.CustomerAddress1 = c.CustomerAddress1,d.CustomerAddress2 = c.CustomerAddress2,d.CustomerAddress3 = c.CustomerAddress3,d.CustomerID = c.CustomerID,d.SAPID = c.SAPID,d.RegionCode = c.RegionCode,d.ZoneCode = c.ZoneCode,d.TerritoryCode = c.TerritoryCode,d.StateCode = c.StateCode,d.TownCode = c.TownCode,d.TypeCode = c.TypeCode,d.PSnonPS = c.PSnonPS,d.EmailID = c.EmailID,d.MobileLL = c.MobileLL,d.SuppliedBy = c.SuppliedBy,d.DrugLicense = c.DrugLicense,d.CurrentYTD = c.CurrentYTD,d.DSByChannelCode = c.DSByChannelCode,d.DSByStrategicCode = c.DSByStrategicCode,d.ClassificationCode = c.ClassificationCode,d.IsActive = c.IsActive,d.DeactiveDt = c.DeactiveDt,d.ActiveDt = c.ActiveDt,d.AnnualIncentivePlan = c.AnnualIncentivePlan,d.isTarangUmang = c.isTarangUmang,d.WholeSalerCode = c.WholeSalerCode,d.isIME = c.isIME,d.createdBy = c.createdBy,d.NKACStores = c.NKACStores,d.ParentCustomerCode = c.ParentCustomerCode,d.IsDirectAcct = c.IsDirectAcct,d.ABICode = c.ABICode,d.DistributorCode = c.DistributorCode,d.DistributorSAPID = c.DistributorSAPID,d.SAPZoneCode = c.SAPZoneCode,d.SAPTerritoryCode = c.SAPTerritoryCode,d.IsConfirm = c.IsConfirm,d.NkacRegion = c.NkacRegion,d.NkacZone = c.NkacZone,d.NkacTerritory = c.NkacTerritory,d.Channel = c.Channel,d.GLN = c.GLN,d.TransitDays = c.TransitDays,d.TransitHours = c.TransitHours,d.VMRApply = c.VMRApply from [DistributorPortal].cdgmaster.Customer d JOIN cdgmaster.Customer c On d.CustomerCode = c.CustomerCode
END
Else If @destinationrecords = @sourcerecords
Begin
Update d Set d.CustomerName = c.CustomerName,d.CustomerAddress1 = c.CustomerAddress1,d.CustomerAddress2 = c.CustomerAddress2,d.CustomerAddress3 = c.CustomerAddress3,d.CustomerID = c.CustomerID,d.SAPID = c.SAPID,d.RegionCode = c.RegionCode,d.ZoneCode = c.ZoneCode,d.TerritoryCode = c.TerritoryCode,d.StateCode = c.StateCode,d.TownCode = c.TownCode,d.TypeCode = c.TypeCode,d.PSnonPS = c.PSnonPS,d.EmailID = c.EmailID,d.MobileLL = c.MobileLL,d.SuppliedBy = c.SuppliedBy,d.DrugLicense = c.DrugLicense,d.CurrentYTD = c.CurrentYTD,d.DSByChannelCode = c.DSByChannelCode,d.DSByStrategicCode = c.DSByStrategicCode,d.ClassificationCode = c.ClassificationCode,d.IsActive = c.IsActive,d.DeactiveDt = c.DeactiveDt,d.ActiveDt = c.ActiveDt,d.AnnualIncentivePlan = c.AnnualIncentivePlan,d.isTarangUmang = c.isTarangUmang,d.WholeSalerCode = c.WholeSalerCode,d.isIME = c.isIME,d.createdBy = c.createdBy,d.NKACStores = c.NKACStores,d.ParentCustomerCode = c.ParentCustomerCode,d.IsDirectAcct = c.IsDirectAcct,d.ABICode = c.ABICode,d.DistributorCode = c.DistributorCode,d.DistributorSAPID = c.DistributorSAPID,d.SAPZoneCode = c.SAPZoneCode,d.SAPTerritoryCode = c.SAPTerritoryCode,d.IsConfirm = c.IsConfirm,d.NkacRegion = c.NkacRegion,d.NkacZone = c.NkacZone,d.NkacTerritory = c.NkacTerritory,d.Channel = c.Channel,d.GLN = c.GLN,d.TransitDays = c.TransitDays,d.TransitHours = c.TransitHours,d.VMRApply = c.VMRApply from [DistributorPortal].cdgmaster.Customer d JOIN cdgmaster.Customer c On d.CustomerCode = c.CustomerCode
END
End Try
Begin Catch
Print Error_Message()
End Catch
END
Please do let me know other way.As I am stuck on this.