January 27, 2010 at 12:28 pm
My table as follow,
CREATE TABLE DERPosi_201001
(
[TID] int not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201001 CHECK (
recGrp BETWEEN '20100101' and '20100131'
)
)
alter table DERPosi_201001 add primary key clustered(TID,recGrp)
CREATE TABLE DERPosi_201002
(
[TID] int not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201002 CHECK (
recGrp BETWEEN '20100201' and '20100228'
)
)
alter table DERPosi_201002 add primary key clustered(TID,recGrp)
CREATE TABLE DERPosi_201003
(
[TID] int not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201003 CHECK (
recGrp BETWEEN '20100301' and '20100331'
)
)
alter table DERPosi_201003 add primary key clustered(TID,recGrp)
After table created, me apply Partitioned Views as follow,
create view dbo.DERPosi with schemabinding
as
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201001
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201002
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201003;
Let's say, there's 2 table has been created using same design as above with name as
DERPosi_201004
DERPosi_201005
So far, what I'm doing is
1. Me execute
ALTER view [dbo].[DERPosi] with schemabinding
as
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201001
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201002
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201003
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201004
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201005
2. This Alter View I perform manually
My Question is,
1. If another DERPosi table created, How my SQL statement looks like to Alter View Dynamically? My DERPosi table format is DERPosi_YYYYMM
Looking for help
January 29, 2010 at 7:59 am
Hi,
I would create a stored procedure to do it based around this:
SELECT @sql = STUFF(
(
SELECT ' SELECT TID,SeatN,Posi,recGrp FROM dbo.' AS "text()",
[name] AS "text()",
' UNION ALL '
FROM sys.tables
WHERE [name] LIKE 'DERPosi%'
FOR XML PATH('')
),1,1, 'CREATE VIEW dbo.DERPosi WITH SCHEMABINDING AS ')
-- Remove the final UNION ALL
SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 10)
January 29, 2010 at 10:22 am
Hi Sir,
tq very much. It's work.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply