Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
446 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

661 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