0

I have two databases, I want to move some history data from a fact table to another database, the destination table is exactly the same as the source table including all the constrains.

I use a SSIS package to transfer the data as below:

  1. first use OLE DB Source to select the data from the source for the required period.
  2. load it to temp table using OLE DB Destination into the second database.

  3. Then load it to the final table using Execute SQL Task

    but I get below error

Error: Violation of PRIMARY KEY constraint 'PK__Financia__362B520524BEA57A'. Cannot insert duplicate key in object 'Fact.FinancialTransactions'. The duplicate key value is (100001 , 2010012, Dec 31 2010 12:00AM, 65, 88).

How do I get around this issue? I want to keep the constrains in the destination table.

Kristijan Iliev
  • 4,901
  • 10
  • 28
  • 47
Sabby
  • 14
  • 7
  • You need to specify what the issue is. What *do* you want to do with the duplicate keys? Update the existing entry, create another one, ignore it? Are you copying data that has already been copied? In this case you can ignore the duplicates - in fact you shouldn't have copied them at all. – Panagiotis Kanavos Jun 18 '15 at 06:55

1 Answers1

0

You may want to add one more execute SQL task which disables the constraints for some time period.But problem is if you disable primary key,you cant do any operations on a table,so dropping also can be an option ,but recreating again will take time.So best option would be to fix the error or rebuild your index by below

ALTER TABLE t1 REBUILD WITH (IGNORE_DUP_KEY = ON)

this will allow duplicates to be ignored.more info here.Can I set ignore_dup_key on for a primary key?

Community
  • 1
  • 1
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94