Solved

Copy three months records of about 20 million of data from 200 million of records using the date range  and some where condition

Posted on 2011-02-22
3
424 Views
Last Modified: 2012-06-27
Hi I have a query where I have to copy the ~ 2 millions of records to another dummy table from 200 millions of records with some condition  with ID as Clustered Primary key

These are Based on two conditions:

1. Recent 3 months of data needs to be copied based on the date range... datatype is datetime which is in the form of following: '2010-02-11 01:25:00.670'

2. There is Description column which says 'Unknown Code 0'. Copy other than those records.

. How can I define the range of date by not hard coding the values I just want to pass the range of date by passing into variables using @min and @max

Is it easy to acheive using temp table or CTE's or is there any easy method that can copy huge data.

Thanks,


0
Comment
Question by:srionline2k6
3 Comments
 

Author Comment

by:srionline2k6
ID: 34950112
Thing to  add more. How can I define the range of date by not hard coding the values I just want to pass the range of date by passing into variables using @min and @max
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 34950749
Can't you have non-clustered index on your date column? Also if you can add description in include list of that non-clustered index. If so then u could use between construct in ur where clause to filter records for 3 months as per @min and @max along with description. You could temporarily hold these selected records in temp table that is again cluster indexed on ur Id. From there use them the way you desire.
0
 
LVL 9

Accepted Solution

by:
sureshbabukrish earned 125 total points
ID: 34951030
DECLARE @ReportStartDate DATETIME            
DECLARE @ReportEndDate DATETIME            
SET @ReportStartDate = DateAdd(M,-3,DateAdd(D,DateDiff(D,0,Getdate()),0))    
SET @ReportEndDate = DateAdd(MS,-3,DateAdd(D,DateDiff(D,0,Getdate()),0))    

the above date range gets last 3 months , if today date is 22nd feb, then nov22nd to feb 21st  will be pulled.

select * from  table1
where datecol between    @ReportStartDate,@ReportEndDate
and description <> 'Unknown Code 0'

above query will pull last three months data and can be used in any insert query or to create any view.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SSRS Calculation help 6 28
MS SQL 2014 get record ID after record is inserted. 12 36
GRANT, REVOKE, DENY 4 17
sql query Help 12 17
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now