Jayanth_Kurup (8/2/2015)
seems fairly simple , i would personally use partitioning since it handles the whole process with far less stress on the DB but it would be just as easy to create an ssis package to do the same. If you could share the table structure I am sure I can drum something up in a few hours.
Here is the table structure. It is ugly. I did not create it.
Thank you for your help.
CREATE TABLE [dbo].[tblCall](
[Call_ID] [int] NOT NULL,
[Call_Date] [datetime] NULL,
[Call_Time] [datetime] NULL,
[Operator_ID] [smallint] NULL,
[Reviewer_ID] [char](30) NULL,
[Insurer_ID] [int] NULL,
[DivisionID] [int] NULL,
[Insurer_Name] [char](50) NULL,
[Insurer_Approval_Comments] [text] NULL,
[Insurer_Daily_Comments] [text] NULL,
[Adjuster] [char](30) NULL,
[CSR_ID] [char](30) NULL,
[CSR_Name] [char](30) NULL,
[CSR_Phone] [char](50) NULL,
[CSR_Ext] [char](10) NULL,
[CSR_Conf] [char](1) NULL,
[CSR_Conf_Date] [datetime] NULL,
[CSR_Conf_Time] [datetime] NULL,
[Insured_Full_NM] [char](50) NULL,
[Insured_FName] [char](14) NULL,
[Insured_LName] [char](16) NULL,
[Insured_Address] [char](30) NULL,
[Insured_City] [char](20) NULL,
[Insured_State] [char](2) NULL,
[Insured_Zip] [char](10) NULL,
[Insured_HPhone] [char](50) NULL,
[Insured_WPhone] [char](50) NULL,
[Insured_CellPhone] [char](50) NULL,
[Insured_Pager] [char](50) NULL,
[Insured_Email] [char](100) NULL,
[Insured_AltPhone] [char](50) NULL,
[Insured_Comments] [text] NULL,
[ThirdParty_Claimant] [char](50) NULL,
[ThirdParty_Phone] [char](50) NULL,
[ThirdParty_AccessName] [char](50) NULL,
[ThirdParty_AccessPhone] [char](50) NULL,
[Policy] [char](25) NULL,
[Claim] [char](25) NULL,
[Deductible_Amt] [money] NULL,
[Loss_Date] [datetime] NULL,
[Loss_Location] [char](30) NULL,
[Loss_Address] [char](30) NULL,
[Loss_City] [char](20) NULL,
[Loss_State] [char](2) NULL,
[Loss_Zip] [char](10) NULL,
[Loss_Descr] [text] NULL,
[Loss_Comments] [text] NULL,
[CONTR_ID] [int] NULL,
[Contractor_Name] [char](50) NULL,
[Contr_Rep] [char](30) NULL,
[Contr_Fax_Date] [datetime] NULL,
[Contr_Fax_Rec_Date] [datetime] NULL,
[Contr_Fax_Rec_Time] [datetime] NULL,
[Contr_Insured_Date] [datetime] NULL,
[Contr_Insured_Time] [datetime] NULL,
[Contr_Appt_Date] [datetime] NULL,
[Contr_Appt_Time] [datetime] NULL,
[Contr_Comments] [text] NULL,
[Contr_GetJob] [char](1) NULL,
[Contr_Cancellation] [char](1) NULL,
[Contr_Assigned_Date] [datetime] NULL,
[PRISM_Just_Dev] [char](1) NULL,
[PRISM_Frst_Est_Date] [datetime] NULL,
[PRISM_Frst_RevReq_Date] [datetime] NULL,
[PRISM_Received_Date] [datetime] NULL,
[PRISM_Closed_Date] [datetime] NULL,
[PRISM_Inspection_Date] [datetime] NULL,
[PRISM_FollowUp_Date] [datetime] NULL,
[PRISM_Comments] [text] NULL,
[PRISM_Frst_Review_Date] [datetime] NULL,
[PRISM_ClaimClosed_Date] [datetime] NULL,
[Owner_ID] [int] NULL,
[DateRec_ID] [int] NULL,
[Creator_ID] [int] NOT NULL,
[CALL_LST_REV_ID_NB] [int] NULL,
[CALL_LST_REV_SER_NB] [int] NULL,
[CALL_LOCK_ID_NB] [int] NULL,
[CALL_BIX_FILE_NM] [varchar](80) NULL,
[CALL_LST_SENT_DT] [datetime] NULL,
[CALL_RCV_EN_IN] [char](1) NULL,
[Est_System] [char](3) NULL,
[CALL_STAT_CD] [char](20) NULL,
[Contr_WorkAuthorization] [char](1) NULL,
[Contr_EMail] [varchar](80) NULL,
[PRISM_ClaimCancellation] [char](1) NULL,
[Loss_Location_Same] [char](1) NULL,
[Nexus_ID] [int] NULL,
[Loss_Cause] [int] NULL,
[CommitDate_Con] [datetime] NULL,
[CommitDate_Ins] [datetime] NULL,
[CommitDate_Est] [datetime] NULL,
[Insured_Company] [varchar](50) NULL,
[PRISM_Frst_RevRec_Date] [datetime] NULL,
[CommitDate_Rev] [datetime] NULL,
[Type_ID] [int] NULL,
[Has_Been_Cancelled] [tinyint] NULL,
[Claim_Cancelled_Reason] [int] NULL,
[Program_Option_ID] [int] NULL,
[ClientAdminResource] [int] NULL,
[CustSurveyTag] [tinyint] NULL,
[ReinspectTag] [tinyint] NULL,
[RandomNum] [real] NULL,
[BilledClient] [tinyint] NULL,
[BilledContractor] [tinyint] NULL,
[billedContrDate] [datetime] NULL,
[billedAssign] [tinyint] NULL,
[billedAssignDate] [datetime] NULL,
[USAAregionName] [varchar](200) NULL,
[singleTradeListID] [int] NULL,
[IsDirty] [tinyint] NULL,
[custSurveyPendDate] [datetime] NULL,
[callDateFull] [datetime] NULL,
[roofInspectionFlg] [bit] NULL,
[USAAprogramArea] [varchar](200) NULL,
[xactTransactionIDValue] [varchar](50) NULL,
[profileCode] [varchar](5) NULL,
[contrEmailSentFlg] [bit] NULL,
[Reinforcement_FLG] [bit] NULL,
[JobStartOverrideDate] [datetime] NULL,
[THDSentFlg] [bit] NULL,
[AddlEstNeeded] [bit] NULL,
[CheckPointPriceList] [varchar](25) NULL,
[PriceList] [varchar](25) NULL,
[ReferralFlg] [bit] NULL,
[MRPSupplementDate] [datetime] NULL,
[CATAssignment] [bit] NULL,
[RvwFlg] [bit] NULL,
[CMS_NextFollowUpDate] [datetime] NULL,
[TestFlg] [bit] NULL,
[CatCode] [varchar](10) NULL,
[PendingCancellationFlg] [bit] NULL,
[PendingNCFFlg] [bit] NULL,
[ReferralId] [varchar](25) NULL,
[CallCenterRepAssigned] [int] NULL,
[isBeingHeld] [bit] NULL,
[XADataSet] [varchar](100) NULL,
[CC_CATCode] [varchar](30) NULL,
[SymbilityClaimId] [uniqueidentifier] NULL,
[SymbilityClaimAssignmentId] [int] NULL,
[SymbilityIntermediateClaimAssignmentId] [int] NULL,
[Loss_BuildYear] [int] NULL,
[Target_Followup_Date] [datetime] NULL,
CONSTRAINT [PK_tblCall] PRIMARY KEY CLUSTERED
(
[Call_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG1]
) ON [FG1] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__Operato__0CEFF2A1] DEFAULT (0) FOR [Operator_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__Insurer__0DE416DA] DEFAULT (0) FOR [Insurer_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__Deducti__0ED83B13] DEFAULT (0) FOR [Deductible_Amt]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__CONTR_I__0FCC5F4C] DEFAULT (0) FOR [CONTR_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_Contr_Cancella1__82] DEFAULT ('N') FOR [Contr_Cancellation]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__CALL_RC__10C08385] DEFAULT ('N') FOR [CALL_RCV_EN_IN]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_PRISM_ClaimCan2__82] DEFAULT ('N') FOR [PRISM_ClaimCancellation]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_Program_Option_ID] DEFAULT (1) FOR [Program_Option_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_ReinspectTag] DEFAULT (0) FOR [ReinspectTag]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_RandomNum] DEFAULT (rand()) FOR [RandomNum]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_BilledClient] DEFAULT (0) FOR [BilledClient]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_BilledContractor] DEFAULT (0) FOR [BilledContractor]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_billedAssign] DEFAULT (0) FOR [billedAssign]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_IsDirty] DEFAULT (1) FOR [IsDirty]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_roofInspectionFlg] DEFAULT (0) FOR [roofInspectionFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_contrEmailSentFlg] DEFAULT (0) FOR [contrEmailSentFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_THDSentFlg] DEFAULT (0) FOR [THDSentFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD DEFAULT ((0)) FOR [CATAssignment]
GO
ALTER TABLE [dbo].[tblCall] ADD DEFAULT ((0)) FOR [RvwFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD DEFAULT ((0)) FOR [PendingCancellationFlg]
GO
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/