Saturday, February 25, 2012

Problem copying tables to another server with Primary and Foreign Key constrains

How can I copy a table with a Primary Key to another server's table of
the same name without receiving the following error: (Error at
Destination for Row number 489. Errors encountered so far in this
task:1.The statement has been terminated. Violation of PRIMARY KEY
constraint 'PK_INDIVIDUALS'. Cannot insert duplicate key in object
'INDIVIDUALS'.)
I want to copy about 20 interrelated cascaded tables with Primary and
Foreign Keys to another server's tables with the identical structure
and not receive the above error. I suggested dropping the tables and
recreating them, but my co-worker feel that this would not work because
of the interrelationship of the tables.
I tried to manually delete a table that is part of this group and
received Error 3726: Could not drop object 'dbo.INDIVIDUALS' because
it is referenced by a FOREIGN KEY constraint.
THE REASON FOR MY INQUIRY IS BECAUSE WE HAVE BEEN EXPERIENCING PROBLEM
WITH REPLICATION AND THESE TABLES ARE THE ARTICLES THAT WE BE
REPLICATED.
Any Suggestion?Some odd questions:
What field is the primary key? Is it the replication id? Is it an Identity
Field ? Some other field?
Instead of deleteing the target table you could try truncating it.
Your error says you are trying to load a duplicate key. The only answers are
Delete, Truncate, Drop or Remove Constraint.
What is the problem you are trying to solve? If it is just having a copy of
the database 'somewhere else', you could consider a backup and restore cycle.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"war_wheelan@.yahoo.com" wrote:
> How can I copy a table with a Primary Key to another server's table of
> the same name without receiving the following error: (Error at
> Destination for Row number 489. Errors encountered so far in this
> task:1.The statement has been terminated. Violation of PRIMARY KEY
> constraint 'PK_INDIVIDUALS'. Cannot insert duplicate key in object
> 'INDIVIDUALS'.)
> I want to copy about 20 interrelated cascaded tables with Primary and
> Foreign Keys to another server's tables with the identical structure
> and not receive the above error. I suggested dropping the tables and
> recreating them, but my co-worker feel that this would not work because
> of the interrelationship of the tables.
> I tried to manually delete a table that is part of this group and
> received Error 3726: Could not drop object 'dbo.INDIVIDUALS' because
> it is referenced by a FOREIGN KEY constraint.
> THE REASON FOR MY INQUIRY IS BECAUSE WE HAVE BEEN EXPERIENCING PROBLEM
> WITH REPLICATION AND THESE TABLES ARE THE ARTICLES THAT WE BE
> REPLICATED.
> Any Suggestion?
>|||What field is the primary key? Is it the replication id? Is it an
Identity Field ? Some other field? THE PRIMARY KEY VARIES FROM TABLE
TO TABLE.
Instead of deleteing the target table you could try truncating it. I
AM NOT A T-SQL PROGRAMMER SO IN NON PROGRAMMING TERMS - CAN I DROP THE
KEYS ON THE DESTINATION TABLE(S) I.E. PRIMARY AND FOREIGN KEYS THEN
COPY THE NEW TABLES TO THE DESTINATION? WOULD THIS RE-ESTABLISH THE
ORIGINAL PRIMARY AND FOREIGN KEYS WITHOUT CORRUPTING THE
DATABASE/TABLES AND THEIR RELATIONSHIPS?
Your error says you are trying to load a duplicate key. The only
answers are Delete, Truncate, Drop or Remove Constraint. SAME QUESTION
AS ABOVE, BUT PERHAPS THE REMOVE CONTRAINT OPTION WOULD WORK. NEW
QUESTION: WOULD SQL LET ME REMOVE A CONTRAINT WITH INTER-RELATIONSHIPS
TO OTHER TABLES OR WOULD IT FAIL BECAUSE OF THE INTER-RELATIONSHIPS.
What is the problem you are trying to solve? If it is just having a
copy of the database 'somewhere else', you could consider a backup and
restore cycle. THE PROLEM IS THAT THESE TABLES WOULD BE REPLICATE IF
REPLICATION WERE WORKING. REPLICATION HAS NOT BEEN WORKING FOR ABOUT A
MONTH SO THE PRIMARY AND SECONDARY DATABASES ARE OUT OF SYNC. IF THE
PRIMARY SERVER GOES DOWN WE WOULD BE MISSING A MONTHS WORTH OF CHANGES.
ERGO BACK TO MY ORIGINAL QUESTION (I want to copy about 20
interrelated cascaded tables with Primary and Foreign Keys to another
server's tables with the identical structure and not receive the above
error.
Thanks for you response. I am trying to get suggestions for my PART
TIME T-SQL programmer.

No comments:

Post a Comment