Unarchive records
-
I'm not fell
ing very fell.
I'm trying to unarchive records from database PrismDataArchive to PrimData from table tblCallArchiveArchive to PrismData tblCall.
SELECT *
FROM PrismDataArchive.dbo.tblcallArchive AS arc
LEFT JOIN PrismData.dbo.tblcall AS Pri ON arc.Call.ID = Pri.Call_ID
WHERE pri.Call_ID IS NULL
This is so simple.
What am I doing wrong.
I have been felling bad lately.
sg 207, Level 16, State 1, Line 35
Invalid column name 'Call'
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/ -
Looks like a typo.
arc.Call.ID = Pri.Call_ID
-
I need to insert records into PrismData tblcall from 2010 that are in the archive table.
Easy no brainer but I got the flu.
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/ -
I can't get it to work.
I have done this numerous time without difficulty.
INSERT INTO PrismData.dbo.tblcall
(Call_ID,
Call_Date,
Call_Time,
Operator_ID,
Reviewer_ID,
Insurer_ID,
DivisionID,
Insurer_Name,
Insurer_Approval_Comments,
Insurer_Daily_Comments,
Adjuster,
CSR_ID,
CSR_Name,
CSR_Phone,
CSR_Ext,
CSR_Conf,
CSR_Conf_Date,
CSR_Conf_Time,
Insured_Full_NM,
Insured_FName,
Insured_LName,
Insured_Address,
Insured_City,
Insured_State,
Insured_Zip,
Insured_HPhone,
Insured_WPhone,
Insured_CellPhone,
Insured_Pager,
Insured_Email,
Insured_AltPhone,
Insured_Comments,
ThirdParty_Claimant,
ThirdParty_Phone,
ThirdParty_AccessName,
ThirdParty_AccessPhone,
Policy,
Claim,
Deductible_Amt,
Loss_Date,
Loss_Location,
Loss_Address,
Loss_City,
Loss_State,
Loss_Zip,
Loss_Descr,
Loss_Comments,
CONTR_ID,
Contractor_Name,
Contr_Rep,
Contr_Fax_Date,
Contr_Fax_Rec_Date,
Contr_Fax_Rec_Time,
Contr_Insured_Date,
Contr_Insured_Time,
Contr_Appt_Date,
Contr_Appt_Time,
Contr_Comments,
Contr_GetJob,
Contr_Cancellation,
Contr_Assigned_Date,
PRISM_Just_Dev,
PRISM_Frst_Est_Date,
PRISM_Frst_RevReq_Date,
PRISM_Received_Date,
PRISM_Closed_Date,
PRISM_Inspection_Date,
PRISM_FollowUp_Date,
PRISM_Comments,
PRISM_Frst_Review_Date,
PRISM_ClaimClosed_Date,
Owner_ID,
DateRec_ID,
Creator_ID,
CALL_LST_REV_ID_NB,
CALL_LST_REV_SER_NB,
CALL_LOCK_ID_NB,
CALL_BIX_FILE_NM,
CALL_LST_SENT_DT,
CALL_RCV_EN_IN,
Est_System,
CALL_STAT_CD,
Contr_WorkAuthorization,
Contr_EMail,
PRISM_ClaimCancellation,
Loss_Location_Same,
Nexus_ID,
Loss_Cause,
CommitDate_Con,
CommitDate_Ins,
CommitDate_Est,
Insured_Company,
PRISM_Frst_RevRec_Date,
CommitDate_Rev,
Type_ID,
Has_Been_Cancelled,
Claim_Cancelled_Reason,
Program_Option_ID,
ClientAdminResource,
CustSurveyTag,
ReinspectTag,
RandomNum,
BilledClient,
BilledContractor,
billedContrDate,
billedAssign,
billedAssignDate,
USAAregionName,
singleTradeListID,
IsDirty,
custSurveyPendDate,
callDateFull,
roofInspectionFlg,
USAAprogramArea,
xactTransactionIDValue,
profileCode,
contrEmailSentFlg,
Reinforcement_FLG,
JobStartOverrideDate,
THDSentFlg,
AddlEstNeeded,
CheckPointPriceList,
PriceList,
ReferralFlg,
MRPSupplementDate,
CATAssignment,
RvwFlg,
CMS_NextFollowUpDate,
TestFlg,
CatCode,
PendingCancellationFlg,
PendingNCFFlg,
ReferralId,
CallCenterRepAssigned,
isBeingHeld,
XADataSet,
CC_CATCode,
SymbilityClaimId,
SymbilityClaimAssignmentId,
SymbilityIntermediateClaimAssignmentId,
Loss_BuildYear,
Target_Followup_Date,
AssignmentReceiptMethodID,
TermsAndConditionsFlg
)
SELECT Call_ID,
Call_Date,
Call_Time,
Operator_ID,
Reviewer_ID,
Insurer_ID,
DivisionID,
Insurer_Name,
Insurer_Approval_Comments,
Insurer_Daily_Comments,
Adjuster,
CSR_ID,
CSR_Name,
CSR_Phone,
CSR_Ext,
CSR_Conf,
CSR_Conf_Date,
CSR_Conf_Time,
Insured_Full_NM,
Insured_FName,
Insured_LName,
Insured_Address,
Insured_City,
Insured_State,
Insured_Zip,
Insured_HPhone,
Insured_WPhone,
Insured_CellPhone,
Insured_Pager,
Insured_Email,
Insured_AltPhone,
Insured_Comments,
ThirdParty_Claimant,
ThirdParty_Phone,
ThirdParty_AccessName,
ThirdParty_AccessPhone,
Policy,
Claim,
Deductible_Amt,
Loss_Date,
Loss_Location,
Loss_Address,
Loss_City,
Loss_State,
Loss_Zip,
Loss_Descr,
Loss_Comments,
CONTR_ID,
Contractor_Name,
Contr_Rep,
Contr_Fax_Date,
Contr_Fax_Rec_Date,
Contr_Fax_Rec_Time,
Contr_Insured_Date,
Contr_Insured_Time,
Contr_Appt_Date,
Contr_Appt_Time,
Contr_Comments,
Contr_GetJob,
Contr_Cancellation,
Contr_Assigned_Date,
PRISM_Just_Dev,
PRISM_Frst_Est_Date,
PRISM_Frst_RevReq_Date
PRISM_Received_Date,
PRISM_Closed_Date,
PRISM_Inspection_Date,
PRISM_FollowUp_Date,
PRISM_Comments,
PRISM_Frst_Review_Date,
PRISM_ClaimClosed_Date,
Owner_ID,
DateRec_ID,
Creator_ID,
CALL_LST_REV_ID_NB,
CALL_LST_REV_SER_NB,
CALL_LOCK_ID_NB,
CALL_BIX_FILE_NM,
CALL_LST_SENT_DT,
CALL_RCV_EN_IN,
Est_System,
CALL_STAT_CD,
Contr_WorkAuthorization,
Contr_EMail,
PRISM_ClaimCancellation,
Loss_Location_Same,
Nexus_ID,
Loss_Cause,
CommitDate_Con,
CommitDate_Ins,
CommitDate_Est,
Insured_Company,
PRISM_Frst_RevRec_Date,
CommitDate_Rev,
Type_ID,
Has_Been_Cancelled,
Claim_Cancelled_Reason,
Program_Option_ID,
ClientAdminResource,
CustSurveyTag,
ReinspectTag,
RandomNum,
BilledClient,
BilledContractor,
billedContrDate,
billedAssign,
billedAssignDate,
USAAregionName,
singleTradeListID,
IsDirty,
custSurveyPendDate,
callDateFull,
roofInspectionFlg,
USAAprogramArea,
xactTransactionIDValue,
profileCode,
contrEmailSentFlg,
Reinforcement_FLG,
JobStartOverrideDate,
THDSentFlg,
AddlEstNeeded,
CheckPointPriceList,
PriceList,
ReferralFlg,
MRPSupplementDate,
CATAssignment,
RvwFlg,
CMS_NextFollowUpDate,
TestFlg,
CatCode,
PendingCancellationFlg,
PendingNCFFlg,
ReferralId,
CallCenterRepAssigned,
isBeingHeld,
XADataSet,
CC_CATCode,
SymbilityClaimId,
SymbilityClaimAssignmentId,
SymbilityIntermediateClaimAssignmentId,
Loss_BuildYear,
Target_Followup_Date,
AssignmentReceiptMethodID,
TermsAndConditionsFlg
FROM PrismDataArchive.dbo.tblcallArchive AS arc
LEFT JOIN PrismData.dbo.tblcall AS Pri ON arc.Call_ID = Pri.Call_ID
WHERE pri.Call_ID IS NULL
(Call_ID,
Call_Date,
Call_Time,
Operator_ID,
Reviewer_ID,
Insurer_ID,
DivisionID,
Insurer_Name,
Insurer_Approval_Comments,
Insurer_Daily_Comments,
Adjuster,
CSR_ID,
CSR_Name,
CSR_Phone,
CSR_Ext,
CSR_Conf,
CSR_Conf_Date,
CSR_Conf_Time,
Insured_Full_NM,
Insured_FName,
Insured_LName,
Insured_Address,
Insured_City,
Insured_State,
Insured_Zip,
Insured_HPhone,
Insured_WPhone,
Insured_CellPhone,
Insured_Pager,
Insured_Email,
Insured_AltPhone,
Insured_Comments,
ThirdParty_Claimant,
ThirdParty_Phone,
ThirdParty_AccessName,
ThirdParty_AccessPhone,
Policy,
Claim,
Deductible_Amt,
Loss_Date,
Loss_Location,
Loss_Address,
Loss_City,
Loss_State,
Loss_Zip,
Loss_Descr,
Loss_Comments,
CONTR_ID,
Contractor_Name,
Contr_Rep,
Contr_Fax_Date,
Contr_Fax_Rec_Date,
Contr_Fax_Rec_Time,
Contr_Insured_Date,
Contr_Insured_Time,
Contr_Appt_Date,
Contr_Appt_Time,
Contr_Comments,
Contr_GetJob,
Contr_Cancellation,
Contr_Assigned_Date,
PRISM_Just_Dev,
PRISM_Frst_Est_Date,
PRISM_Frst_RevReq_Date
PRISM_Received_Date,
PRISM_Closed_Date,
PRISM_Inspection_Date,
PRISM_FollowUp_Date,
PRISM_Comments,
PRISM_Frst_Review_Date,
PRISM_ClaimClosed_Date,
Owner_ID,
DateRec_ID,
Creator_ID,
CALL_LST_REV_ID_NB,
CALL_LST_REV_SER_NB,
CALL_LOCK_ID_NB,
CALL_BIX_FILE_NM,
CALL_LST_SENT_DT,
CALL_RCV_EN_IN,
Est_System,
CALL_STAT_CD,
Contr_WorkAuthorization,
Contr_EMail,
PRISM_ClaimCancellation,
Loss_Location_Same,
Nexus_ID,
Loss_Cause,
CommitDate_Con,
CommitDate_Ins,
CommitDate_Est,
Insured_Company,
PRISM_Frst_RevRec_Date,
CommitDate_Rev,
Type_ID,
Has_Been_Cancelled,
Claim_Cancelled_Reason,
Program_Option_ID,
ClientAdminResource,
CustSurveyTag,
ReinspectTag,
RandomNum,
BilledClient,
BilledContractor,
billedContrDate,
billedAssign,
billedAssignDate,
USAAregionName,
singleTradeListID,
IsDirty,
custSurveyPendDate,
callDateFull,
roofInspectionFlg,
USAAprogramArea,
xactTransactionIDValue,
profileCode,
contrEmailSentFlg,
Reinforcement_FLG,
JobStartOverrideDate,
THDSentFlg,
AddlEstNeeded,
CheckPointPriceList,
PriceList,
ReferralFlg,
MRPSupplementDate,
CATAssignment,
RvwFlg,
CMS_NextFollowUpDate,
TestFlg,
CatCode,
PendingCancellationFlg,
PendingNCFFlg,
ReferralId,
CallCenterRepAssigned,
isBeingHeld,
XADataSet,
CC_CATCode,
SymbilityClaimId,
SymbilityClaimAssignmentId,
SymbilityIntermediateClaimAssignmentId,
Loss_BuildYear,
Target_Followup_Date,
AssignmentReceiptMethodID,
TermsAndConditionsFlg
)
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/ -
Looks like you fixed the typo.
But telling us that you "can't get it to work" is inviting caustic responses. What is the error you are getting now?
-
Totally screwed up.
SELECT *
FROM PrismDataArchive.dbo.tblcallArchive AS arc
LEFT JOIN PrismData.dbo.tblcall AS Pri ON arc.Call.ID = Pri.Call_ID
WHERE PrismData.dbo.tblcall Pri.Call_ID IS NULL
Msg 4145, Level 15, State 1, Line 13
An expression of non-boolean type specified in a context where a condition is expected, near 'Pri'.
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/ -
Welsh Corgi (12/24/2015)
Totally screwed up.SELECT *
FROM PrismDataArchive.dbo.tblcallArchive AS arc
LEFT JOIN PrismData.dbo.tblcall AS Pri ON arc.Call.ID = Pri.Call_ID
WHERE PrismData.dbo.tblcall Pri.Call_ID IS NULL
Msg 4145, Level 15, State 1, Line 13
An expression of non-boolean type specified in a context where a condition is expected, near 'Pri'.
For some reason, the phrase 'moving target' comes to mind.
-
This should work.
- You didn't need the full name of the tblcall table in the WHERE clause, only the alias + column name.
- Also, in the JOIN clause, you should be using arc.Call_ID not arc.Call.ID.
- Finally, I'm assuming you only need to return columns from the tblCallArchive table and tblCall is only for comparison, so include appropriate table alias before the asterisk in SELECT clause.
SELECT arc.*
FROM PrismDataArchive.dbo.tblcallArchive AS arc
LEFT JOIN PrismData.dbo.tblcall AS Pri ON arc.Call_ID = Pri.Call_ID
WHERE Pri.Call_ID IS NULL
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Phil Parkin (12/24/2015)
Welsh Corgi (12/24/2015)
Totally screwed up.SELECT *
FROM PrismDataArchive.dbo.tblcallArchive AS arc
LEFT JOIN PrismData.dbo.tblcall AS Pri ON arc.Call.ID = Pri.Call_ID
WHERE PrismData.dbo.tblcall Pri.Call_ID IS NULL
Msg 4145, Level 15, State 1, Line 13
An expression of non-boolean type specified in a context where a condition is expected, near 'Pri'.
For some reason, the phrase 'moving target' comes to mind.
Sorry Phil.
I have been working around the clock nearly three weeks and I'm sick.
No excuse.
The last post took care on the SELECT I'm getting errors on the INSERT.
SQL Server keeps locking up on me.
I'm going threw hell.
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/ -
Now I get the following error:
BEGIN TRANSACTION
INSERT INTO PrismData.dbo.tblcall
SELECT TOP 5000 arc.*
FROM PrismDataArchive.dbo.tblcallArchive AS arc
LEFT JOIN PrismData.dbo.tblcall AS Pri ON arc.Call_ID = Pri.Call_ID
WHERE Pri.Call_ID IS NULL
--AND YEAR(arc.Call_Date) IN (2010, 2011)
AND YEAR(arc.Call_Date) IN (2010)
-- COMMIT TRANSACTION
-- ROLLBACK TRANSACTION
(1 row(s) affected)
Msg 2627, Level 14, State 1, Procedure trg__tblcall__XactControlPointMatrix, Line 31
Violation of PRIMARY KEY constraint 'PK_XactControlPointMatrix'. Cannot insert duplicate key in object 'dbo.XactControlPointMatrix'. The duplicate key value is (805549).
The statement has been terminated
I do not get it.
Happy holidays and thanks for the help.:-)
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/ -
There are triggers on the tables so I was told that I have to do the insert using a Bulk Insert from the command line.
How do you do that?
There are 2 records out a thousand that I need to insert into the table to unarchive.
Any help would be greatly appreciated:
This is the code to unarchive by year but I need a WHILE with a batch size but for now I just nee to do it filtering by tblCall.dbo.call_ID.
Merry Christmas.
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/ -
To bypass triggers can I do it in SSIS?
Is there not an option or do I have to do it from the command line?
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/ -
The easiest thing to do would be to disable trigger, insert in one shot, and then re-enable trigger.
I cant believe they've got you doing this b.s. on Christmas Eve. You deserve better than this, pup.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Eric M Russell (12/24/2015)
The easiest thing to do would be to disable trigger, insert in one shot, and then re-enable trigger.I cant believe they've got you doing this b.s. on Christmas Eve. You deserve better than this, pup.
Well I got off an hour ago.
They are not making me do it.
The AVP that I work for has been out and he will not return until the New Year.
He and a guy that has been here for 16 years told me not to worry about it over the holidays and had made the decision on the archive years not me.
There are about 16 triggers.
I'm going to write an unArchive script put it into a table and do a fast table Load in SSIS which will bypass the triggers but proably not tonight.
Thank and Merry Christmas!
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/ -
INSERT INTO PrismData.dbo.tblcall
SELECT * -- TOP 5000 arc.*
FROM PrismDataArchive.dbo.tblcallArchive AS arc
WHERE YEAR(arc.Call_Date) IN (1998)
COMMIT TRANSACTION
-- ROLLBACK TRANSACTION
Msg 213, Level 16, State 1, Line 12
Column name or number of supplied values does not match table definition.
I have compared the structures and they are the same.
Any ideas?
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/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply