Contraint issue in SP while loading throug ssis package manually disabled constraints and enablebing after running SP

Vikram Dasar 1 Reputation point
2022-04-26T08:18:28.707+00:00

In SP they have written same logic as below but while executing SP throwing constraint issue for merge statement can we any alternate way to over come this issue. just to mention when we disabling constraints for specific tables then if i run sp it is running fine need some ideas how to ovecome this issue.

-- begin of insert using merge
insert into dbo.tblDimSCDType2Example
( --Table and columns in which to insert the data
  SourceID1,
  SourceID2,
  Attribute1,
  Attribute2,
  Check_Sum,
  EffectiveDate,
  EndDate
)
-- Select the rows/columns to insert that are output from this merge statement 
-- In this example, the rows to be inserted are the rows that have changed (UPDATE).
select    
SourceID1,
SourceID2,
Attribute1,
Attribute2,
Check_Sum,
EffectiveDate,
EndDate
from
(
  -- This is the beginning of the merge statement.
  -- The target must be defined, in this example it is our slowly changing
  -- dimension table
  MERGE into dbo.tblDimSCDType2Example AS target
  -- The source must be defined with the USING clause
  USING 
  (
    -- The source is made up of the attribute columns from the staging table.
    SELECT 
    SourceID1,
    SourceID2,
    Attribute1,
    Attribute2,
    Check_Sum
    from dbo.tblStaging
  ) AS source 
  ( 
    SourceID1,
    SourceID2,
    Attribute1,
    Attribute2,
    Check_Sum
  ) ON --We are matching on SourceID1 and SourceID2 in the target table and the source table.
  (
    target.SourceID1 = source.SourceID1
    and target.SourceID2 = source.SourceID2
  )
  -- If the ID's match but the CheckSums are different, then the record has changed;
  -- therefore, update the existing record in the target, end dating the record 
  -- and set the CurrentRecord flag to N
  WHEN MATCHED and target.Check_Sum <> source.Check_Sum 
               and target.CurrentRecord='Y'
  THEN 
  UPDATE SET 
    EndDate=getdate()-1, 
    CurrentRecord='N', 
    LastUpdated=getdate(), 
    UpdatedBy=suser_sname()
  -- If the ID's do not match, then the record is new;
  -- therefore, insert the new record into the target using the values from the source.
  WHEN NOT MATCHED THEN  
  INSERT 
  (
    SourceID1, 
    SourceID2,
    Attribute1,
    Attribute2,
    Check_Sum
  )
  VALUES 
  (
    source.SourceID1, 
    source.SourceID2,
    source.Attribute1,
    source.Attribute2,
    source.Check_Sum
  )
  OUTPUT $action, 
    source.SourceID1, 
    source.SourceID2,
    source.Attribute1,
    source.Attribute2,
    source.Check_Sum,
    getdate(),
    '12/31/9999'
) -- the end of the merge statement
--The changes output below are the records that have changed and will need
--to be inserted into the slowly changing dimension.
as changes 
(
  action, 
  SourceID1, 
  SourceID2,
  Attribute1,
  Attribute2,
  Check_Sum,
  EffectiveDate,
  EndDate
)
where action='UPDATE';
Azure SQL Database
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
517 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} votes