Dynamic query to loop through the Day table partitions and Dynamic query to loop through the YearMonth table partitions

TestDataMart and in that I have day wise tables like

Testtable_20111101

Testtable_20111102

Testtable_20111103

.

.

.

Testtable_20111130

I need to query on all the tables and need to get the result.

Below is the Simple Dynamic Query to do that:

Create

table #temp

(

[ID1]

int,

[ID2]

[ID3]

[ID4]

int, int, int

)

Declare

@YearMonthDay

@sql nvarchar(1024), char(8)

set

@YearMonthDay = 20111101

WHILE

@YearMonthDay <= 20111130

BEGIN

set

select '

(select COUNT(*) from TestDataMart.dbo.Testtable_'

where pagenameid=''-707294222'' and RefererDomain=''m.facebook.com'') as [No.Of.Mobiles],

(select COUNT(*) from TestDataMart.dbo.Testtable_'

where pagenameid=''-707294222'' and UserAgent like ''%iphone%'') as [No.Of.IPhones]

from TestDataMart.dbo.Testtable_'

where pagenameid=''-707294222'''

@sql = N'insert into #temp + @YearMonthDay +' as [Date], COUNT(*) as [ID1],+@YearMonthDay+' with(nolock)+@YearMonthDay+' with(nolock)+@YearMonthDay+' with(nolock)

exec

sp_executesql @sql

SELECT

@YearMonthDay = @YearMonthDay+1

END

select

*from #temp

　

Dynamic query to loop through the YearMonth table partitions:

Below is the query to loop through to the YearMonth partition tables

.

Create table #temp

(

[ID1]

int,

[ID2]

int,

)

Declare

@YearMonth

@sql nvarchar(1024), char(8)

set

@YearMonth = 201107

WHILE

@YearMonth <= 201112

BEGIN

set

select '

(select COUNT(*) from TestDataMart.dbo.Testtable'

@sql = N'insert into #temp + @YearMonth +' as [Date], +@YearMonth+' with(nolock)) '

exec

sp_executesql @sql

SELECT

@YearMonth = @YearMonth+1

END

select

　

*from #temp

OutPut:

ID1 ID2

201107 33842355

201108 35960333

201109 33066366

201110 31964109

201111 29932711

201112 19528365

Dynamic query to loop through the Day table partitions

Ex:

I have the Data Mart Called