Insertion faild in partitioned View in Sql server 7.0

[Balbir Singh]

I have a problem while I try to insert data into a partioned view I am
getting the following error.

Server: Msg 4436, Level 16, State 12, Line 9
UNION ALL view 'sales_all' is not updatable because a partitioning column
was not found.

Any thoughts

USE pubs

CREATE TABLE sales_monthly
( sales_month int NOT NULL ,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_jan
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_feb
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO

ALTER TABLE sales_feb WITH NOCHECK ADD
CONSTRAINT PK_sales_feb PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_feb CHECK (sales_month = 2)
GO

ALTER TABLE sales_jan WITH NOCHECK ADD
CONSTRAINT PK_sales_jan PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_jan CHECK (sales_month = 1)
GO

[Jun]

If your goal is to partition the data from one table into two table and you have a column (sale_month) to identify which month the sales made, I would use the following statement to do the update:


/***** After you generated your data in Monthly_sales table
You can insert your data to two partition table *****/

INSERT INTO Sales_jan
(sales_month, sales_qty)
SELECT sales_month, sales_qty
FROM Sales_monthly
WHERE sales_month = 1
GO

INSERT INTO Sales_Feb
(sales_month, sales_qty)
SELECT sales_month, sales_qty
FROM Sales_monthly
WHERE sales_month = 2
GO

.
.
.
.
.

-- You can INSERT data to all your 12 month sales tables.
-- OR use a Loop to do you insert processes

Hope this helps!

Jun



------------
Balbir Singh at 12/8/00 1:25:45 PM

Well , Thanks for reply,
Sorry for not sending you the full script. Here is the full script. Actually I am updating more than one table through views. I had seen the online help and it is mentioned that we can update the partitioned views.


USE pubs

CREATE TABLE sales_monthly
( sales_month int NOT NULL ,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_jan
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_feb
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO

ALTER TABLE sales_feb WITH NOCHECK ADD
CONSTRAINT PK_sales_feb PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_feb CHECK (sales_month = 2)
GO

ALTER TABLE sales_jan WITH NOCHECK ADD
CONSTRAINT PK_sales_jan PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_jan CHECK (sales_month = 1)
GO



CREATE VIEW sales_all AS
SELECT sales_month , sales_qty FROM sales_jan
UNION all
SELECT sales_month , sales_qty FROM sales_feb

GO

INSERT INTO sales_monthly VALUES ( 1 , 10)
INSERT INTO sales_monthly VALUES ( 2 , 20)
INSERT INTO sales_monthly VALUES ( 1 , 30)
INSERT INTO sales_monthly VALUES ( 2 , 40)
INSERT INTO sales_monthly VALUES ( 2 , 10)


INSERT INTO sales_all
(sales_month , sales_qty)
SELECT sales_month , sales_qty FROM sales_monthly







------------
Jun at 12/8/00 9:59:33 AM

If your View is a result of table unions, you will have hard time to do an insert to more one table at a time. If your INSERT statement only inserts the columns to one table (one table only), you will be fine. I don't know what's your situation.

Jun

------------
Balbir Singh at 12/7/00 8:59:56 PM


I have a problem while I try to insert data into a partioned view I am
getting the following error.

Server: Msg 4436, Level 16, State 12, Line 9
UNION ALL view 'sales_all' is not updatable because a partitioning column
was not found.

Any thoughts

USE pubs

CREATE TABLE sales_monthly
( sales_month int NOT NULL ,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_jan
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_feb
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO

ALTER TABLE sales_feb WITH NOCHECK ADD
CONSTRAINT PK_sales_feb PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_feb CHECK (sales_month = 2)
GO

ALTER TABLE sales_jan WITH NOCHECK ADD
CONSTRAINT PK_sales_jan PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_jan CHECK (sales_month = 1)
GO

[Balbir Singh]

Actually, the idea is that we have to use partitioned view to update the tables. Lets say that Sales_Jan ,Sales_Feb will be residing on different servers and the partitioned view is on the different server. so when we update the partitioned view and then this will update the different tables on different servers. The reason behind is that we have only one object to update(Partitioned view) not so many objects.

I got the information that partitioned view feature is not availabe in sql server 7.0 but it is availabe in sql 2000. I tried in sql 2000 it works fine.

I really appreciate your help. If I found any difficulty I will contact you.
Thanks again


------------
Jun at 12/8/00 2:23:29 PM


If your goal is to partition the data from one table into two table and you have a column (sale_month) to identify which month the sales made, I would use the following statement to do the update:


/***** After you generated your data in Monthly_sales table
You can insert your data to two partition table *****/

INSERT INTO Sales_jan
(sales_month, sales_qty)
SELECT sales_month, sales_qty
FROM Sales_monthly
WHERE sales_month = 1
GO

INSERT INTO Sales_Feb
(sales_month, sales_qty)
SELECT sales_month, sales_qty
FROM Sales_monthly
WHERE sales_month = 2
GO

.
.
.
.
.

-- You can INSERT data to all your 12 month sales tables.
-- OR use a Loop to do you insert processes

Hope this helps!

Jun



------------
Balbir Singh at 12/8/00 1:25:45 PM

Well , Thanks for reply,
Sorry for not sending you the full script. Here is the full script. Actually I am updating more than one table through views. I had seen the online help and it is mentioned that we can update the partitioned views.


USE pubs

CREATE TABLE sales_monthly
( sales_month int NOT NULL ,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_jan
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_feb
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO

ALTER TABLE sales_feb WITH NOCHECK ADD
CONSTRAINT PK_sales_feb PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_feb CHECK (sales_month = 2)
GO

ALTER TABLE sales_jan WITH NOCHECK ADD
CONSTRAINT PK_sales_jan PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_jan CHECK (sales_month = 1)
GO



CREATE VIEW sales_all AS
SELECT sales_month , sales_qty FROM sales_jan
UNION all
SELECT sales_month , sales_qty FROM sales_feb

GO

INSERT INTO sales_monthly VALUES ( 1 , 10)
INSERT INTO sales_monthly VALUES ( 2 , 20)
INSERT INTO sales_monthly VALUES ( 1 , 30)
INSERT INTO sales_monthly VALUES ( 2 , 40)
INSERT INTO sales_monthly VALUES ( 2 , 10)


INSERT INTO sales_all
(sales_month , sales_qty)
SELECT sales_month , sales_qty FROM sales_monthly







------------
Jun at 12/8/00 9:59:33 AM

If your View is a result of table unions, you will have hard time to do an insert to more one table at a time. If your INSERT statement only inserts the columns to one table (one table only), you will be fine. I don't know what's your situation.

Jun

------------
Balbir Singh at 12/7/00 8:59:56 PM


I have a problem while I try to insert data into a partioned view I am
getting the following error.

Server: Msg 4436, Level 16, State 12, Line 9
UNION ALL view 'sales_all' is not updatable because a partitioning column
was not found.

Any thoughts

USE pubs

CREATE TABLE sales_monthly
( sales_month int NOT NULL ,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_jan
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_feb
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO

ALTER TABLE sales_feb WITH NOCHECK ADD
CONSTRAINT PK_sales_feb PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_feb CHECK (sales_month = 2)
GO

ALTER TABLE sales_jan WITH NOCHECK ADD
CONSTRAINT PK_sales_jan PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_jan CHECK (sales_month = 1)
GO