Dear Team,

I have a job for Data Integration. We fetches the row data by linked server and normalize it and insert the normalize data into our table. 
and if the same record is present in the table then we update that record by unique number maching in both source and destination table.
but the problem is that it will update all the record in the table which is already present in the table. suppose i have a table which contains 10000 records and i am inserting 11000 records by job and there is 10000 records is already present in the destination table, then it will insert only 1000 records and rest of 10000 records will be updated. i dont want to update all 10000 records in destination table i want to update only that records which is updated in source table. i.e if 1 record is updated in source table i want to update only one record don't want to update all 10000 records because it causes problem while there is huge amount of data.

i have sample procedure below

CREATE PROCEDURE SegmentIntergration
AS
BEGIN
IF OBJECT_ID ('tempDB..#AngelSegment') IS NOT NULL
DROP TABLE #AngelSegment
select  
sbb.refno [RegAprRefNo]
,sbb.sbtag [RegTAG]
,(CASE 
WHEN enum.Segment = 'NSE_FNO' AND sbb.ORGType = 'P' THEN 'NIFA'
WHEN enum.Segment = 'NSE_CASH' AND sbb.ORGType = 'P' THEN 'NICS'
WHEN enum.Segment = 'BSE_CASH' AND sbb.ORGType = 'P' THEN 'BICS'
WHEN enum.Segment = 'BSE_FNO' AND sbb.ORGType = 'P' THEN 'BIFA'
WHEN enum.Segment = 'BSE_CASH' AND sbb.ORGType = 'CO' THEN 'BCCS'
WHEN enum.Segment = 'NSE_FNO' AND sbb.ORGType = 'CO' THEN 'NCFA'
WHEN enum.Segment = 'NSE_CASH' AND sbb.ORGType = 'CO' THEN 'NCCS'
WHEN enum.Segment = 'BSE_FNO' AND sbb.ORGType = 'CO' THEN 'BCFA'
WHEN enum.Segment = 'BSE_CASH' AND sbb.ORGType = 'I' THEN 'BICS'
WHEN enum.Segment = 'NSE_CASH' AND sbb.ORGType = 'I' THEN 'NICS'
WHEN enum.Segment = 'NSE_FNO' AND sbb.ORGType = 'I' THEN 'NIFA'
WHEN enum.Segment = 'BSE_FNO' AND sbb.ORGType = 'I' THEN 'BIFA'
WHEN enum.Segment = 'BSE_CASH' AND sbb.ORGType = 'PF' THEN 'BPCS'
WHEN enum.Segment = 'BSE_FNO' AND sbb.ORGType = 'PF' THEN 'BPFA'
WHEN enum.Segment = 'NSE_FNO' AND sbb.ORGType = 'PF' THEN 'NPFA'
WHEN enum.Segment = 'NSE_CASH' AND sbb.ORGType = 'PF' THEN 'NPCS'

END) [RegExchangeSegment]
,NULL [NameSalutation]
,ISNULL(sbp.FirstName,'') +''+ISNULL(sbp.MiddleName,'') +''+isnull(sbp.LastName,'') [RegName]
,NULL [TradeNameSalutation]
,seg.TradeName [RegTradeName]
,sbp.FathHusbName [RegFatherHusbandName]
,sbb.ORGType [Type]
,sbp.DOB [RegDOB]
,sbp.Sex [RegSex]
,(select (tmp.AddLine1+ ''+tmp.AddLine1) [RegResAdd1] from [196.1.115.219].[SB_COMP].dbo.sb_contact tmp WHERE tmp.refno = sbb.refno AND tmp.AddType = 'RES' ) [RegResAdd1] 
,(select (tmp1.AddLine1+ ''+tmp1.AddLine1) [RegResAdd2] from [196.1.115.219].[SB_COMP].dbo.sb_contact tmp1 WHERE tmp1.refno = sbb.refno AND tmp1.AddType = 'OFF' ) [RegResAdd2] 
,NULL [RegResAdd3]
,NULL [RegResCity]
,NULL [RegResPin]
,NULL [RegOffAdd1]
,NULL [RegOffAdd2]
,NULL [RegOffAdd3]
,NULL [RegOffCity]
,NULL [RegOffPin]
,NULL [RegOffPhone]
,sbc.MobNo [RegMobile]
,NULL [RegMobile2]
,NULL [RegResPhone]
,sbc.FaxNo [RegResFax]
,sbb.PanNo [RegPAN]
,NULL [RegPAN_Applied]
,NULL [RegCorrespondanceAdd]
,NULL [RegMAPIN]
,NULL [RegProprietorshipYN]
,NULL [RegExpinYrs]
,NULL [RegResidentialStatus]
,sbc.Emailid [RegEmailId]
,sbb.ParentTag [RegReferenceTAG]
,NULL [RegMkrId]
,NULL [RegMkrDt]
,sbb.BRANCH [TAGBranch]
,NULL [GLUnregisteredcode]
,NULL [GLRegisteredCode]
,(CASE WHEN (app.name = 'Remiser Applied'
OR app.name = 'SB Applied'
OR app.name = 'AP Applied') THEN 'Applied To Exchange'
WHEN (app.name = 'Remiser Approved'
OR app.name = 'SB Approved'
OR app.name = 'AP Approved') THEN 'Registered'
WHEN (app.name = 'Remiser Rejected'
OR app.name = 'SB Rejected'
OR app.name = 'AP Rejected') THEN 'Rejected By Exchange'
END )[Regstatus]
,NULL [Regdate]
,ststusType.SebiRegistrationNo [RegNo]
,NULL [upload]
,NULL [Alias]
,NULL [AliasName]
,NULL [RegPortalNo]
,NULL [RegIntimateDate]
,NULL [BO_UPDATE]
,NULL [CTS]
,NULL [PLS]
,NULL [Address status]
,NULL [Address Intimate Date]
,ststusType.Notes [Reason]
,NULL [FileName]

INTO #AngelSegment
from LinkRefIntermediaryRefSegment seg 
LEFT JOIN LinkLinkRefInterRefSegRefSegAppStatType ststusType ON  ststusType.LinkRefIntermediaryRefSegmentId = seg.LinkRefIntermediaryRefSegmentId 
LEFT JOIN RefSegmentApplicationStatusType app ON app.RefSegmentApplicationStatusTypeId = ststusType.RefSegmentApplicationStatusTypeId
LEFT JOIN RefIntermediary refint ON refint.RefIntermediaryId = seg.RefIntermediaryId
LEFT JOIN RefSegmentEnum enum ON enum.RefSegmentEnumId = seg.RefSegmentId
LEFT JOIN RefIntermediaryType intType ON intType.RefIntermediaryTypeId = refint.RefIntermediaryTypeId
INNER JOIN [196.1.115.219].[SB_COMP].dbo.sb_broker sbb ON sbb.SBtag collate database_default = refint.IntermediaryCode
INNER JOIN [196.1.115.219].[SB_COMP].dbo.sb_personal sbp ON sbp.refno = sbb.refno
INNER JOIN [196.1.115.219].[SB_COMP].dbo.sb_Contact sbc ON sbc.refno = sbp.refno

WHERE --seg.RefIntermediaryId = 249 --and app.Name is not null
ISNULL(ststusType.FromDate, '1-Jan-2100') = ( SELECT
                                                              MAX(ISNULL(temp.FromDate,'1-Jan-2100'))
                                                              FROM
                                                              LinkLinkRefInterRefSegRefSegAppStatType temp
                                                              WHERE
                                                              temp.LinkRefIntermediaryRefSegmentId = ststusType.LinkRefIntermediaryRefSegmentId 
                                                              )
AND refint.AddedOn >= '18-JUN-2015'
;With y
As
(
SELECT *,ROW_NUMBER() OVER (partition by RegAprRefNo, RegTAG, RegExchangeSegment Order by RegAprRefNo, RegTAG, RegExchangeSegment) as RANK FROM #AngelSegment)
delete from y where RANK>1

UPDATE bpreg set 
NameSalutation = tmpseg.NameSalutation,
RegName = tmpseg.RegName,
TradeNameSalutation = tmpseg.TradeNameSalutation,
RegTradeName = tmpseg.RegTradeName,
RegFatherHusbandName = tmpseg.RegFatherHusbandName,
Type = tmpseg.Type,
RegDOB = tmpseg.RegDOB,
RegSex = tmpseg.RegSex,
RegResAdd1 = tmpseg.RegResAdd1,
RegResAdd2 = tmpseg.RegResAdd2,
RegResAdd3 = tmpseg.RegResAdd3,
RegResCity = tmpseg.RegResCity,
RegResPin = tmpseg.RegResPin,
RegOffAdd1 = tmpseg.RegOffAdd1,
RegOffAdd2 = tmpseg.RegOffAdd2,
RegOffAdd3 = tmpseg.RegOffAdd3,
RegOffCity = tmpseg.RegOffCity,
RegOffPin = tmpseg.RegOffPin,
RegOffPhone = tmpseg.RegOffPhone,
RegMobile = tmpseg.RegMobile,
RegMobile2 = tmpseg.RegMobile2,
RegResPhone = tmpseg.RegResPhone,
RegResFax = tmpseg.RegResFax,
RegPAN = tmpseg.RegPAN,
RegPAN_Applied = tmpseg.RegPAN_Applied,
RegCorrespondanceAdd = tmpseg.RegCorrespondanceAdd,
RegMAPIN = tmpseg.RegMAPIN,
RegProprietorshipYN = tmpseg.RegProprietorshipYN,
RegExpinYrs = tmpseg.RegExpinYrs,
RegResidentialStatus = tmpseg.RegResidentialStatus,
RegEmailId = tmpseg.RegEmailId,
RegReferenceTAG = tmpseg.RegReferenceTAG,
RegMkrId = tmpseg.RegMkrId,
RegMkrDt = tmpseg.RegMkrDt,
TAGBranch = tmpseg.TAGBranch,
GLUnregisteredcode = tmpseg.GLUnregisteredcode,
GLRegisteredCode = tmpseg.GLRegisteredCode,
Regstatus = tmpseg.Regstatus,
Regdate = tmpseg.Regdate,
RegNo = tmpseg.RegNo,
upload = tmpseg.upload,
Alias = tmpseg.Alias,
AliasName = tmpseg.AliasName,
RegPortalNo = tmpseg.RegPortalNo,
RegIntimateDate = tmpseg.RegIntimateDate,
BO_UPDATE = tmpseg.BO_UPDATE,
CTS = tmpseg.CTS,
PLS = tmpseg.PLS,
[Address status] = tmpseg.[Address status],
[Address Intimate Date] = tmpseg.[Address Intimate Date],
Reason = tmpseg.Reason,
Filename = tmpseg.Filename
from #angelsegment tmpseg inner join [196.1.115.219].[sb_comp].dbo.bpregmaster bpreg
on tmpseg.regaprrefno = bpreg.regaprrefno COLLATE DATABASE_DEFAULT
and tmpseg.regtag = bpreg.regtag COLLATE DATABASE_DEFAULT
AND tmpSeg.RegExchangeSegment = bpReg.RegExchangeSegment COLLATE DATABASE_DEFAULT

INSERT INTO [196.1.115.219].[sb_comp].dbo.bpregmaster 
(
[RegAprRefNo],
[RegTAG],
[RegExchangeSegment],
[NameSalutation],
[RegName],
[TradeNameSalutation],
[RegTradeName],
[RegFatherHusbandName],
[Type],
[RegDOB],
[RegSex],
[RegResAdd1],
[RegResAdd2],
[RegResAdd3],
[RegResCity],
[RegResPin],
[RegOffAdd1],
[RegOffAdd2],
[RegOffAdd3],
[RegOffCity],
[RegOffPin],
[RegOffPhone],
[RegMobile],
[RegMobile2],
[RegResPhone],
[RegResFax],
[RegPAN],
[RegPAN_Applied],
[RegCorrespondanceAdd],
[RegMAPIN],
[RegProprietorshipYN],
[RegExpinYrs],
[RegResidentialStatus],
[RegEmailId],
[RegReferenceTAG],
[RegMkrId],
[RegMkrDt],
[TAGBranch],
[GLUnregisteredcode],
[GLRegisteredCode],
[Regstatus],
[Regdate],
[RegNo],
[upload],
[Alias],
[AliasName],
[RegPortalNo],
[RegIntimateDate],
[BO_UPDATE],
[CTS],
[PLS],
[Address status],
[Address Intimate Date],
[Reason],
[Filename]

)
SELECT 
tmpSegment.RegAprRefNo,
tmpSegment.RegTAG,
tmpSegment.RegExchangeSegment,
tmpSegment.NameSalutation,
tmpSegment.RegName,
tmpSegment.TradeNameSalutation,
tmpSegment.RegTradeName,
tmpSegment.RegFatherHusbandName,
tmpSegment.Type,
tmpSegment.RegDOB,
tmpSegment.RegSex,
tmpSegment.RegResAdd1,
tmpSegment.RegResAdd2,
tmpSegment.RegResAdd3,
tmpSegment.RegResCity,
tmpSegment.RegResPin,
tmpSegment.RegOffAdd1,
tmpSegment.RegOffAdd2,
tmpSegment.RegOffAdd3,
tmpSegment.RegOffCity,
tmpSegment.RegOffPin,
tmpSegment.RegOffPhone,
tmpSegment.RegMobile,
tmpSegment.RegMobile2,
tmpSegment.RegResPhone,
tmpSegment.RegResFax,
tmpSegment.RegPAN,
tmpSegment.RegPAN_Applied,
tmpSegment.RegCorrespondanceAdd,
tmpSegment.RegMAPIN,
tmpSegment.RegProprietorshipYN,
tmpSegment.RegExpinYrs,
tmpSegment.RegResidentialStatus,
tmpSegment.RegEmailId,
tmpSegment.RegReferenceTAG,
tmpSegment.RegMkrId,
tmpSegment.RegMkrDt,
tmpSegment.TAGBranch,
tmpSegment.GLUnregisteredcode,
tmpSegment.GLRegisteredCode,
tmpSegment.Regstatus,
tmpSegment.Regdate,
tmpSegment.RegNo,
tmpSegment.upload,
tmpSegment.Alias,
tmpSegment.AliasName,
tmpSegment.RegPortalNo,
tmpSegment.RegIntimateDate,
tmpSegment.BO_UPDATE,
tmpSegment.CTS,
tmpSegment.PLS,
tmpSegment.[Address status],
tmpSegment.[Address Intimate Date],
tmpSegment.Reason,
tmpSegment.Filename
FROM #angelsegment tmpSegment
WHERE NOT EXISTS(SELECT 1 FROM [196.1.115.219].[sb_comp].dbo.bpregmaster DupCheck 
WHERE DupCheck.RegAprRefNo = tmpSegment.RegAprRefNo --COLLATE DATABASE_DEFAULT
AND DupCheck.RegTAG = tmpSegment.RegTAG COLLATE DATABASE_DEFAULT
AND DupCheck.RegExchangeSegment = tmpSegment.RegExchangeSegment COLLATE DATABASE_DEFAULT)
END

• Edited by Chetan Vishwakarma 23 hours 19 minutes ago

If there is a column in the source data that is guaranteed to change on any update, you could save that column in your destination table and compare that column to the source data on update. If the source data comes from SQL Server, that would be column of the type timestamp (also known as rowversion, and it has nothing to do with time).

If there is a last_updated_time column, you need to tread more carefully. You need to verify that this column is properly updated, which is non-trivial to achieve on SQL Server.

Some people go for checksum(*) or binary_checksum(*). However, this is not very reliable, as the checksum algorithm that SQL Server uses is fairly simple-minded and the likelihood that two different values will have the same checksum cannot be ignored. I definitely recommend against this.

And then you add one big huge WHERE clause that goes:

WHERE (bpreg.RegResAdd1 = tmpseg.RegResAdd1 OR
       bpreg.RegResAdd1 IS NULL AND tmpseg.RegResAdd1 IS NOT NULL OR
       bpreg.RegResAdd1 IS NOT NULL tmpseg.RegResAdd1 IS NULL)
  AND (....

You can skip the IS NULL checks for the non-nullable columns.

Writing that code is tedious, but you could generate it.

Do you have any column in the source table which indicates when data was updated latest. This column could be some datetime column.This way you fetch the latest updated record from source and similarly update those records in destination.

Example - Pseudo code

1. Source database - Column containing Latest update Timestamp

2. Based on the latest timestamp pickup those records

3. And update those records in destination database

Hi,

You perhaps need to use Hashbytes. Hashbytes are way better than checksums. You may choose to use the SHA_256 algo. Here is what you can do

Have a Binary(32) column in your destination table. 

Have a staging table which also needs to have a binary(32) column (can be a heap i.e. without indexes) . Dump the contents of the source into the staging table and calculate the Hashbyte values and store in that hashbyte column in the staging table.

Compare this calculated hashbyte column in the staging table with the stored hashbyte value in the destination table's column and only if there is a change, do the update including the value of the Hashbyte column in the destination table.

This should be ok and with the Hashbyte SHA_256 chances of collision is almost ruled out.

Hope that helps.

Yes, when it comes to collisions hashbytes should be fine.

But:

1) There is no hashbytes(*) to compute a hash for the entire row.  So you need to concatenate the values into a string or binary value, which

2) Is about as unwieldy as comparing the column.

3) And the input is limited to 8000 bytes, which means that it is a non-starter if you have LOB columns or several long string columns in the table.

However, what could be alternative is to write a CLR function that computes a hash using .NET functions, that (I presume) does not have the 8000 bytes limitation. However:

1) To be practical, the CLR function would only accept the key values and read the row itself, but this would not perform extremely well.

2) Passing all columns would make it performant - but again unwieldy with all those columns.

There is a possible work around, though. Form a string of all columns with FOR XML RAW and compute the hash on the XML string which you pass the CLR function.

Now, could the XML function be used for a T-SQL solution? Maybe. At least if there are no LOB columns. Chop up the XML documents in slices of 8000 bytes and send to hashbytes and store multiple columns.