On Jan 25, 12:31 am, "Kyle" <knygro...@gmail.com> wrote:
> I have had a cascading series of problems stemming from partitioned
> tables with identity columns, and it's ended at Instead Of triggers and
> the inserted virtual table needing to be referenced from dynamic sql.
> SQL Server 2000.
>
> I've got partitioned tables and a partitioned view, and each of the
> partitioned tables has an identity column (each table properly seeded
> so id column is unique across all tables). This existence of the
> identity columns prevents the partitioned view from being insertable (I
> don't say updatable, because the partitioned view accepts updates and
> deletes just fine).
>
> So, I have created an Instead Of trigger on the partitioned view and I
> will properly distribute the values to the correct partitioned table
> upon insert myself if SQL Server 2K won't do it for me. However, the
> name of the table to insert into is dependent on one of the values in
> the insert statement - obviously, because this is a partitioned view
> and by definition there must be a partitioned column that determines
> base table. So, I want to look at the value of the partitioned column
> in the inserted virtual table in order to determine which base table to
> insert into.
>
> That's easy, and then I create SQL to insert the data into the
> appropriate base table, but it must be dynamic SQL because the name of
> the table must be determined at runtime. Only one row will be inserted
> at a time so I don't have to worry about bulk inserts.
>
> select @dt = max(event_date) from inserted
> Execute sp_executesql N'INSERT INTO ' + dbo.GetPartitionTableName(@dt)
> + ' select a, b, c, d from inserted'
>
> This does not work, presumably because inserted is a virtual table. Is
> there any way to solve this problem? Insert performance is critical,
> so copying to a real temp table is not an option. It is OK to be
> forced to use a dummy for the identity field in the insert (as the
> instead of trigger mandates), but it is not acceptable to change all
> the client code that currently is 'insert into massive
> unpartitionedtable' to 'figure out which base table applies' and
> 'insert into partitionedbasetable'. That must be abstracted from the
> client calls.
>
> Any help is mucho mucho appreciated.

wrote:

>
>Don't do it - static SQL performs much better in such cases:
>
> INSERT INTO Sales2005(...) SELECT ... FROM INSERTED WHERE '20050101' <=
> SaleDate AND SaleDate < '20060101'
> INSERT INTO Sales2006(...) SELECT ... FROM INSERTED WHERE '20060101' <=
> SaleDate AND SaleDate < '20070101'
> INSERT INTO Sales2007(...) SELECT ... FROM INSERTED WHERE '20070101' <=
> SaleDate AND SaleDate < '20080101'
>
> -----------------------
> Alex Kuznetsovhttp://sqlserver-tips.blogspot.com/http://sqlserver-puzzles.blogspot.com/- Hide quoted text -- Show quoted text -