Partitioning on Multiple Columns

Has anyone successfully implement partitioning on multiple columns? My
attempts produce the following error:
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
updatable because a partitioning column was not found.
For example:
CREATE TABLE [dbo].[Test_200312_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200312_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1016)
GO
ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1016)
GO
CREATE VIEW [dbo].[Test_pvw]
AS
SELECT * FROM [dbo].[Test_200312_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200312_1016_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1016_ptb]
INSERT INTO [dbo].[Test_pvw](
Period,
BusinessUnit,
Amount)
VALUES(
'200312',
1015,
100.00)
DROP VIEW [dbo].[Test_pvw]
GO
DROP TABLE [dbo].[Test_200312_1015_ptb]
GO
DROP TABLE [dbo].[Test_200312_1016_ptb]
GO
DROP TABLE [dbo].[Test_200401_1015_ptb]
GO
DROP TABLE [dbo].[Test_200401_1016_ptb]
GOPlease don't start new posts on issues that are being answered. Please
continue with the thread you already started in .programming.
SK
Robert S. Wallace wrote:
>Has anyone successfully implement partitioning on multiple columns? My
>attempts produce the following error:
>Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
>updatable because a partitioning column was not found.
>
>For example:
>CREATE TABLE [dbo].[Test_200312_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200312_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
> CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
> CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1016)
>GO
>ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
> CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
> CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1016)
>GO
>CREATE VIEW [dbo].[Test_pvw]
>AS
> SELECT * FROM [dbo].[Test_200312_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200312_1016_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1016_ptb]
>INSERT INTO [dbo].[Test_pvw](
> Period,
> BusinessUnit,
> Amount)
>VALUES(
> '200312',
> 1015,
> 100.00)
>DROP VIEW [dbo].[Test_pvw]
>GO
>DROP TABLE [dbo].[Test_200312_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200312_1016_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1016_ptb]
>GO
>
>