sql-server - T-SQL:UNION ALL View 不可更新，因为未找到分区列

如何在具有日期限制的视图中插入？

这是单击脚本作为创建表后得到的我的表：

Table 1：

CREATE TABLE [dbo].[tbl_zaua_1_17](
    [id] [int] NOT NULL,
    [date] [datetime] NULL,

     CONSTRAINT [PK_tbl_zaua_1_17] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_zaua_1_17]
WITH CHECK ADD  CONSTRAINT [CK_tbl_zaua_1_17]

CHECK  (([date]<'2014-01-18 00:00:00.000' AND [date]>'2014-01-16 00:00:00.000'))
GO

ALTER TABLE [dbo].[tbl_zaua_1_17] CHECK CONSTRAINT [CK_tbl_zaua_1_17]
GO`

Table 2：

CREATE TABLE [dbo].[tbl_zaua_1_11](
    [id] [int] NOT NULL,
    [date] [datetime] NULL,
 CONSTRAINT [PK_tbl_zaua_1_11] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_zaua_1_11]  WITH CHECK ADD  CONSTRAINT [CK_tbl_zaua_1_11] CHECK  (([date]<'2014-01-12 00:00:00.000' AND [date]>'2014-01-10 00:00:00.000'))
GO

ALTER TABLE [dbo].[tbl_zaua_1_11] CHECK CONSTRAINT [CK_tbl_zaua_1_11]
GO`

视图创建：

create view zaua1
as
   select * from [dbo].[tbl_zaua_1_11]
   union all
   select * from [dbo].[tbl_zaua_1_17]`

插入给出错误：


UNION ALL视图不可更新，因为未找到分区列。

insert into [dbo].[zaua1]
values (3,'2014-01-11')

最佳答案

这个例子可以解决你的问题

create table partA
    (
    partId int,
    type varchar(10) constraint CKpartA_type check (type = 'partA'),
    value int,
    constraint PKpartA primary key(partId, type),
    )
    create table partB
    (
    partId int,
    type varchar(10) constraint CKpartB_type check (type = 'partB'),
    value int,
    constraint PKpartB primary key(partId, type)
    )
    go
    create view part
    as
    select partId, type, value
    from partA
    union all
    select partId, type, value
    from partB
    go


    insert into part
    select 1,'partB',1
    union all
    select 2,'partA',2
    go
    update part
    set value = 20
    go

    select *
    from part

    go
    delete from part
    go