asked on

I need to download data from a huge table month wise

Hi Experts,
I need to query a table from production which is huge for the past 6 months, I was suggested to query the prod table by month and load into 6 dev tables, not sure how I can do that?
Would SSIS be a good option? please suggest?

Guy Hengel [angelIII / a3]

the request is quite unclear/unprecise...

the question is, what exactly you have as data and what you need as results...

one option might be to partition the (original) table, to start with:
http://msdn.microsoft.com/en-us/library/ms190787.aspx

Scott Pletcher

What is the clustering index on the original table? If it is month/date[time], as it seems likely it should be, then querying by month makes sense.

I wouldn't load into 6 separate tables in dev. Instead, just be sure to cluster the table by month/date[time].

Russell Fox

See above for long-term solutions, but if this is a one-off project then this will create the new tables as it pulls data from the existing table:

SELECT * 
INTO Production201401
FROM dbo.Production
WHERE CAST([OrderDate] AS DATE) BETWEEN '1/1/2014' AND '1/31/2014'
GO
SELECT * 
INTO Production201402
FROM dbo.Production
WHERE CAST([OrderDate] AS DATE) BETWEEN '2/1/2014' AND '2/28/2014'
GO
-- Etc.

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

Adding for emphasis, do not use BETWEEN for date ranges:

... the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.

Itzik Ben-Gan

This applies to many databases using SQL. Also see: "Beware of Between"

sqlcurious

ASKER

Thanks