question

VikramDasar-7556 avatar image
0 Votes"
VikramDasar-7556 asked pituach edited

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

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';
sql-server-transact-sqlazure-sql-databasesql-server-migration
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Not sure that I understand this, but I read this in haste before signing off.

If you get a constraint violation, you are supposedly violating a business constraint. Or are you saying that you are temporarily violating the constraint during the operation, and everything is OK when you are done?

Could you explain more clearly?

Also, add the button with ones and zeroes to insert code sample, to avoid accidents with Markdown. (The markup used in this forum.)

0 Votes 0 ·

0 Answers