July 14, 2011 at 12:50 pm
I'm stuck with 2008 R2 standard edition and so I can't do true partitioning and have to use a partitioned view.
This is fine, but BOL states:
"INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement."
So how do you insert rows into a partitioned view from another table when you have to reference the view to find the rows that don't exist??
July 14, 2011 at 6:39 pm
graham.balmer (7/14/2011)
So how do you insert rows into a partitioned view from another table when you have to reference the view to find the rows that don't exist??
Find the missing rows from the source and drop their key to a temp table between the selection component and the insert.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 15, 2011 at 1:01 am
Ok. What about updating the partitioned view with another table, can't see how this can be done without referencing the view in the update to find the matching rows?
July 15, 2011 at 11:31 am
graham.balmer (7/15/2011)
Ok. What about updating the partitioned view with another table, can't see how this can be done without referencing the view in the update to find the matching rows?
Can you post the DDL for the table and the views you've built, and the query in question? I want to double check a few things and that'll give me an easier to use test mechanic. It's been a bit since I fought with partitioned views.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 15, 2011 at 2:25 pm
Four base tables for 2008 or earlier, 2009, 2010, 2011 or later - first one shown:
CREATE TABLE [dbo].[Migrate2008HHTable](
[MeterPointRef] [nvarchar](50) NOT NULL,
[MeterRef] [nvarchar](50) NOT NULL,
[Rdg_Timestamp] [smalldatetime] NOT NULL,
[Value] [decimal](18, 4) NOT NULL,
[UtilityType] [varchar](5) NOT NULL,
[ReadingType] [char](1) NULL,
[DataSource] [varchar](20) NULL,
[DataFile] [varchar](50) NULL,
[Row_Added_Timestamp] [smalldatetime] NULL,
[Row_Updated_Timestamp] [smalldatetime] NULL,
CONSTRAINT [PK_Migrate2008HHTable] PRIMARY KEY NONCLUSTERED
(
[MeterPointRef] ASC,
[MeterRef] ASC,
[Rdg_Timestamp] ASC
)
)
ALTER TABLE [dbo].[Migrate2008HHTable] WITH CHECK ADD CONSTRAINT [CK_Migrate2008HHTable_Rdg_Timestamp] CHECK (([Rdg_Timestamp]<'01/01/2009'))
CREATE CLUSTERED INDEX [IX_Migrate2008HHTable_Rdg_Timestamp] ON [dbo].[Migrate2008HHTable]
(
[Rdg_Timestamp] ASC
)
Partitioned view:
CREATE view [dbo].[MigrateViewDestination]
with schemabinding
as
select
MeterPointRef,
MeterRef,
Rdg_Timestamp,
[Value],
UtilityType,
ReadingType,
DataSource,
DataFile,
Row_Added_Timestamp,
Row_Updated_Timestamp
from
dbo.Migrate2008HHTable
union all
select
MeterPointRef,
MeterRef,
Rdg_Timestamp,
[Value],
UtilityType,
ReadingType,
DataSource,
DataFile,
Row_Added_Timestamp,
Row_Updated_Timestamp
from
dbo.Migrate2009HHTable
union all
select
MeterPointRef,
MeterRef,
Rdg_Timestamp,
[Value],
UtilityType,
ReadingType,
DataSource,
DataFile,
Row_Added_Timestamp,
Row_Updated_Timestamp
from
dbo.Migrate2010HHTable
union all
select
MeterPointRef,
MeterRef,
Rdg_Timestamp,
[Value],
UtilityType,
ReadingType,
DataSource,
DataFile,
Row_Added_Timestamp,
Row_Updated_Timestamp
from
dbo.Migrate2011HHTable
Here's the insert of new rows from table MigrateHHProcess which has the same composite PK:
insert MigrateViewDestination
(
MeterPointRef,
MeterRef,
Rdg_Timestamp,
[Value],
UtilityType,
ReadingType,
DataSource,
DataFile,
Row_Added_Timestamp,
Row_Updated_Timestamp
)
select
T.MeterPointRef,
T.MeterRef,
T.Rdg_Timestamp,
T.[Value],
T.UtilityType,
T.ReadingType,
'inserted',
'inserted',
getdate(),
getdate()
from
MigrateHHProcess T
left outer join MigrateViewDestination D on
D.MeterPointRef = T.MeterPointRef and
D.MeterRef = T.MeterRef and
D.rdg_Timestamp = T.rdg_Timestamp
where
D.MeterPointRef is null and
D.MeterRef is null and
D.rdg_Timestamp is null
This fails for reason given in original post, but with help of others found it to work with an instead of insert trigger on the view:
CREATE trigger TR_MigrateViewDestination_InsteadOfInsert
on MigrateViewDestination instead of insert
as
insert MigrateViewDestination
select * from inserted
Finally, here's the update of matching rows (that works without any update trigger):
update MigrateViewDestination
set
[Value] = T.[Value],
UtilityType = T.UtilityType,
ReadingType = T.ReadingType,
DataSource = 'updated',
DataFile = 'updated',
Row_Updated_Timestamp = getdate()
from
MigrateHHProcess T
inner join MigrateViewDestination D on
D.MeterPointRef = T.MeterPointRef and
D.MeterRef = T.MeterRef and
D.rdg_Timestamp = T.rdg_Timestamp
where not(
D.[Value] = T.[Value] and
D.ReadingType = T.ReadingType and
D.UtilityType = T.UtilityType)
July 17, 2011 at 12:01 pm
There's a few problems here. Two of which is going to be seriously problematic:
http://msdn.microsoft.com/en-us/library/ms187067.aspx
INSERT statements add data to the member tables through the partitioned view. The INSERT statements must follow to these rules:
All columns must be included in the INSERT statement even if the column can be NULL in the base table or has a DEFAULT constraint defined in the base table.
The DEFAULT keyword cannot be specified in the VALUES clause of the INSERT statement.
INSERT statements must supply a value that satisfies the logic of the CHECK constraint defined on the partitioning column for one of the member tables.
INSERT statements are not allowed if a member table contains a column with an identity property.
INSERT statements are not allowed if a member table contains a timestamp column.
INSERT statements are not allowed if there is a self-join with the same view or any one of the member tables.
With those two issues, you're pretty much SOL on the direct method. You're going to have to use the same workaround that you'd use for the insert detection, and your'e going to have to restrict your where clause for single sub-table insert per pass.
Sorry it took me a bit, I wanted to make sure I did the research to remember the rules properly before I sent you haring off some random path.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 17, 2011 at 1:04 pm
Thanks.
There doesn't seem to be the restriction of the where clause being for a single sub-table insert per pass (if I understand this right) as a single:
insert PartionedView...
select ... from SourceView
(without any datetime filtering in the where) for the initial move of all the data to the partitioned view works with SourceView rows from 2001 to 2011 - the four base tables.
It's all been a bit more tricky than I expected and I'm a little worried that I'll want to do some data modifications on the partitioned view in the future that suddenly fail. Nevertheless, I'll have to go for it as the table is so big and growing rapidly and the performance gain with the optimiser just hitting the appropriate base table on selects is too good to miss. Enterprise SQL Server would be nice but I can hear the laughter from here...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply