June 28, 2011 at 9:25 am
If any of you saw my post yesterday about an archiving job and how to do the OUTPUT INTO clause, this is along the same lines.
I found after archiving some of my data off to our archive database that some of my records didn't get converted successfully via another process, and therefore were flagged as 'eligible to archive' incorrectly. Since then, my archive job has run and my records are gone from my primary database. Now I need to select those 1337 records and insert them back into my primary database.
When running my script (from yesterday, Delete cl Output Into PrimaryDatabase..TableA From ArchiveDatabase..TableA) I'm getting the above error message. It's throwing this on one of the constraints that is a foreign key for just a lookup table. Why is it throwing that error when the ID that I'm trying to place in that field is valid? Do I have to disable all of the foreign-key constraints on my primary database table in order to run this script?
June 28, 2011 at 9:43 am
Here's the script I'm trying to run that throws the error, the field with the constraint that causes the error is CorrespondenceTemplateID:
Set Identity_Insert IARTS..CorrespondenceLog On
Deleteacl
OutputDeleted.CorrespondenceLogID,
Deleted.CorrespondenceMethodID,
Deleted.CorrespondenceTemplateID,
Deleted.Loc_Prof_ID,
Deleted.Src_Sys_Tx_Type_ID,
Deleted.Tran_ID,
Deleted.CorrespondenceSetID,
Deleted.ReceivedDate,
Deleted.VRTDocumentID,
Deleted.Subject,
Deleted.SentIndicator,
Deleted.Comment,
Deleted.CorrespondenceFO,
Deleted.PrintedDate,
Deleted.PrintByBatch,
Deleted.CreatedBy,
Deleted.CreatedDate,
Deleted.Void,
Deleted.VoidedBy,
Deleted.VoidedDate,
Deleted.ERMSStatus
IntoIARTS..CorrespondenceLog
(
CorrespondenceLogID,
CorrespondenceMethodID,
CorrespondenceTemplateID,
Loc_Prof_ID,
Src_Sys_Tx_Type_ID,
Tran_ID,
CorrespondenceSetID,
ReceivedDate,
VRTDocumentID,
Subject,
SentIndicator,
Comment,
CorrespondenceFO,
PrintedDate,
PrintByBatch,
CreatedBy,
CreatedDate,
Void,
VoidedBy,
VoidedDate,
ERMSStatus
)
FromArchive..CorrespondenceLog acl
Whereacl.CorrespondenceLogID In (1337 CorrespondenceLogIDs)
June 28, 2011 at 9:53 am
Tables that you will Output Into cannot have foreign key constraints, nor can they be referenced by foreign key constraints. There are other rules as well. The MSDN article on Output Into has the details.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 28, 2011 at 9:54 am
Post removed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2011 at 9:57 am
GSquared (6/28/2011)
Tables that you will Output Into cannot have foreign key constraints, nor can they be referenced by foreign key constraints. There are other rules as well. The MSDN article on Output Into has the details.
So should I just do a standard Insert Into...and then delete those same ID's if it's successful?
June 28, 2011 at 10:03 am
Thanks, got it working...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply