Inserting on Partitioned View

Hello, I'm trying to insert on a partitioned view and I get this error:

INSERT INTO dbo.TREP_NOVEDADES
( NMSEC_NOVEDAD,
CDCONCEPTO,
CDTIPO_VALOR,
CDPRECIO,
CDTIP_HECT_DTO_PAG,
FEGENERACION,
FEPAGO_COBRO,
CDMETODO_DISTRIBUC,
CDTIPO_DCTO_PAGO,
VRDESCUENTO_PAGO,
SNTERCERO,
CDCLASE_NOVEDAD,
CDENTIDAD,
CDESTADO,
CDCAUSA,
NMSEC_PREFIJO,
SNPAGO_CAJA,
SNVAL_PROPOR_PESO,
CDTIP_HECT_DISTRIB,
CDMONEDA,
CDCOMPANIA,
NMSEC_NOVEDAD_PADR,
SNGENERADA,
POEMBARGO,
CDTERCERO,
CDCIA_TERCERO,
NMSEM_GENERACION,
NMANO_GENERACION,
NMPER_GENERACION,
NMSEM_PAGOCOBRO,
NMANO_PAGOCOBRO,
NMPER_PAGOCOBRO,
CDTERCERO_CAJA,
CDSUCURSAL_CAJA,
CDCOMPANIA_CAJA)
VALUES ( 3904,
'C01',
'V',
null,
null,
GETDATE(),
GETDATE(),
null,
null,
25999,
'N',
'F',
'016',
'01',
'000001',
null,
'N',
'N',
null,
'PESOC',
'01',
null,
'N',
null,
null,
null,
null,
2007,
null,
null,
null,
null,
null,
null,
null)

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

This is the tables information:

CREATE TABLE [dbo].[TREP_NOVEDADES_2006] (
[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,
[CDCONCEPTO] [varchar] (12) NOT NULL ,
[CDTIPO_VALOR] [char] (1) NULL ,
[CDPRECIO] [varchar] (12) NULL ,
[CDTIP_HECT_DTO_PAG] [varchar] (2) NULL ,
[FEGENERACION] [datetime] NOT NULL ,
[FEPAGO_COBRO] [datetime] NULL ,
[CDMETODO_DISTRIBUC] [char] (1) NULL ,
[CDTIPO_DCTO_PAGO] [char] (1) NULL ,
[VRDESCUENTO_PAGO] [numeric](18, 5) NULL ,
[SNTERCERO] [char] (1) NOT NULL ,
[CDCLASE_NOVEDAD] [char] (1) NOT NULL ,
[CDENTIDAD] [varchar] (3) NOT NULL ,
[CDESTADO] [varchar] (2) NOT NULL ,
[CDCAUSA] [varchar] (6) NOT NULL ,
[NMSEC_PREFIJO] [numeric](12, 0) NULL ,
[SNPAGO_CAJA] [char] (1) NOT NULL ,
[SNVAL_PROPOR_PESO] [char] (1) NOT NULL ,
[CDTIP_HECT_DISTRIB] [varchar] (2) NULL ,
[CDMONEDA] [varchar] (5) NOT NULL ,
[CDCOMPANIA] [varchar] (2) NOT NULL ,
[NMSEC_NOVEDAD_PADR] [numeric](12, 0) NULL ,
[SNGENERADA] [char] (1) NOT NULL ,
[POEMBARGO] [decimal](7, 4) NULL ,
[CDTERCERO] [varchar] (15) NULL ,
[CDCIA_TERCERO] [varchar] (2) NULL ,
[NMSEM_GENERACION] [tinyint] NULL ,
[NMANO_GENERACION] [smallint] NULL ,
[NMPER_GENERACION] [numeric](6, 0) NULL ,
[NMSEM_PAGOCOBRO] [tinyint] NULL ,
[NMANO_PAGOCOBRO] [smallint] NULL ,
[NMPER_PAGOCOBRO] [numeric](6, 0) NULL ,
[CDTERCERO_CAJA] [varchar] (15) NULL ,
[CDSUCURSAL_CAJA] [varchar] (4) NULL ,
[CDCOMPANIA_CAJA] [varchar] (2) NULL ,
[SNGENERAR_TRG] [char] (1) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TREP_NOVEDADES_2006] ADD
CONSTRAINT [DF__TREP_NOVE__SNGEN2006] DEFAULT ('S') FOR [SNGENERAR_TRG],
CONSTRAINT [TREP_NOVEDADES2006_PK] PRIMARY KEY CLUSTERED
(
[NMSEC_NOVEDAD]
) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_2006_NMANOGENER_CK] CHECK ([NMANO_GENERACION] <= 2006),
CONSTRAINT [TREP_NOVEDADES_CDMET_DIST2006_CK] CHECK ([CDMETODO_DISTRIBUC] is null or ([CDMETODO_DISTRIBUC] = 'P' or ([CDMETODO_DISTRIBUC] = 'I' or ([CDMETODO_DISTRIBUC] = 'H' or ([CDMETODO_DISTRIBUC] = 'C' or [CDMETODO_DISTRIBUC] = 'D'))))),
CONSTRAINT [TREP_NOVEDADES_CDTIP_DCTO2006_CK] CHECK ([CDTIPO_DCTO_PAGO] is null or ([CDTIPO_DCTO_PAGO] = 'H' or ([CDTIPO_DCTO_PAGO] = 'C' or [CDTIPO_DCTO_PAGO] = 'A'))),
CONSTRAINT [TREP_NOVEDADES_CDTIPO_VAL2006_CK] CHECK ([CDTIPO_VALOR] is null or ([CDTIPO_VALOR] = 'P' or [CDTIPO_VALOR] = 'V' or [CDTIPO_VALOR] = 'E')),
CONSTRAINT [TREP_NOVEDADES_SNGENERAD2006_CK] CHECK ([SNGENERADA] = 'S' or [SNGENERADA] = 'N')
GO
CREATE TABLE [dbo].[TREP_NOVEDADES_2007] (
[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,
[CDCONCEPTO] [varchar] (12) NOT NULL ,
[CDTIPO_VALOR] [char] (1) NULL ,
[CDPRECIO] [varchar] (12) NULL ,
[CDTIP_HECT_DTO_PAG] [varchar] (2) NULL ,
[FEGENERACION] [datetime] NOT NULL ,
[FEPAGO_COBRO] [datetime] NULL ,
[CDMETODO_DISTRIBUC] [char] (1) NULL ,
[CDTIPO_DCTO_PAGO] [char] (1) NULL ,
[VRDESCUENTO_PAGO] [numeric](18, 5) NULL ,
[SNTERCERO] [char] (1) NOT NULL ,
[CDCLASE_NOVEDAD] [char] (1) NOT NULL ,
[CDENTIDAD] [varchar] (3) NOT NULL ,
[CDESTADO] [varchar] (2) NOT NULL ,
[CDCAUSA] [varchar] (6) NOT NULL ,
[NMSEC_PREFIJO] [numeric](12, 0) NULL ,
[SNPAGO_CAJA] [char] (1) NOT NULL ,
[SNVAL_PROPOR_PESO] [char] (1) NOT NULL ,
[CDTIP_HECT_DISTRIB] [varchar] (2) NULL ,
[CDMONEDA] [varchar] (5) NOT NULL ,
[CDCOMPANIA] [varchar] (2) NOT NULL ,
[NMSEC_NOVEDAD_PADR] [numeric](12, 0) NULL ,
[SNGENERADA] [char] (1) NOT NULL ,
[POEMBARGO] [decimal](7, 4) NULL ,
[CDTERCERO] [varchar] (15) NULL ,
[CDCIA_TERCERO] [varchar] (2) NULL ,
[NMSEM_GENERACION] [tinyint] NULL ,
[NMANO_GENERACION] [smallint] NULL ,
[NMPER_GENERACION] [numeric](6, 0) NULL ,
[NMSEM_PAGOCOBRO] [tinyint] NULL ,
[NMANO_PAGOCOBRO] [smallint] NULL ,
[NMPER_PAGOCOBRO] [numeric](6, 0) NULL ,
[CDTERCERO_CAJA] [varchar] (15) NULL ,
[CDSUCURSAL_CAJA] [varchar] (4) NULL ,
[CDCOMPANIA_CAJA] [varchar] (2) NULL ,
[SNGENERAR_TRG] [char] (1) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TREP_NOVEDADES_2007] ADD
CONSTRAINT [DF__TREP_NOVE__SNGEN] DEFAULT ('S') FOR [SNGENERAR_TRG],
CONSTRAINT [TREP_NOVEDADES2007_PK] PRIMARY KEY CLUSTERED
(
[NMSEC_NOVEDAD]
) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_2007_NMANOGENER_CK] CHECK ([NMANO_GENERACION] > 2006),
CONSTRAINT [TREP_NOVEDADES_CDMET_DIST2007_CK] CHECK ([CDMETODO_DISTRIBUC] is null or ([CDMETODO_DISTRIBUC] = 'P' or ([CDMETODO_DISTRIBUC] = 'I' or ([CDMETODO_DISTRIBUC] = 'H' or ([CDMETODO_DISTRIBUC] = 'C' or [CDMETODO_DISTRIBUC] = 'D'))))),
CONSTRAINT [TREP_NOVEDADES_CDTIP_DCTO2007_CK] CHECK ([CDTIPO_DCTO_PAGO] is null or ([CDTIPO_DCTO_PAGO] = 'H' or ([CDTIPO_DCTO_PAGO] = 'C' or [CDTIPO_DCTO_PAGO] = 'A'))),
CONSTRAINT [TREP_NOVEDADES_CDTIPO_VAL2007_CK] CHECK ([CDTIPO_VALOR] is null or ([CDTIPO_VALOR] = 'P' or [CDTIPO_VALOR] = 'V' or [CDTIPO_VALOR] = 'E')),
CONSTRAINT [TREP_NOVEDADES_SNGENERAD2007A_CK] CHECK ([SNGENERADA] = 'S' or [SNGENERADA] = 'N')
go
CREATE VIEW TREP_NOVEDADES
AS
SELECT * FROM dbo.TREP_NOVEDADES_2006
UNION ALL
SELECT * FROM dbo.TREP_NOVEDADES_2007

My first instinct would be to create an InsteadOf trigger on the view which would include logic to determine which base table each row of data should be inserted into. Obviously you'd have to update the trigger's definition along with the view's when you come to add new tables.

Like I said, this is an initial reaction - I'm not sure if there's a more efficient way of handling this.

Chris

|||

Chris Howarth wrote:

My first instinct would be to create an InsteadOf trigger on the view which would include logic to determine which base table each row of data should be inserted into. Obviously you'd have to update the trigger's definition along with the view's when you come to add new tables.

Like I said, this is an initial reaction - I'm not sure if there's a more efficient way of handling this.

Chris

Hello, I think It's possible to do an UPDATE or INSERT into the view and I'll get my row modified or inserted into the correct base table, depending of the CHECK condition I created before on every base table without creating any trigger...

Isn't It possible..? Do I necessarily have to create the trigger ?

Thanks|||

I think you're right - check out the 'Updatable Partitioned Views' section in the page at this link:

http://msdn2.microsoft.com/en-us/library/aa933141(SQL.80).aspx

There are several criteria that have to be met for INSERTs/UPDATEs/DELETEs to function correctly against this type of View - one of which is that the partitioning column must be part of each of the tables' Primary Key.

If you can't meet the requirements laid out in the above document then your next best option would be to go down the INSTEAD OF TRIGGER route.

Chris