Query regarding Partioned view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query regarding Partioned view

Author

Topic

VipinMitta
Starting Member

12 Posts

Posted - 2009-07-01 : 03:30:15

Hi all,

i have created 3 horizontal partitioned table and also create view with union all . but when i insert 201378 records into table through partitioned view.

it show 2 message

(201378 row(s) affected)

(16 row(s) affected)

why is it showing additional 16 rows affected ?

MY data structure is as follow..........

----------------------------------------

CREATE TABLE [dbo].[TBL1](
[TRAN_CODE] [varchar](12) NOT NULL,
[TR_DATE] [datetime] NOT NULL CHECK ([TR_DATE] < '20060101'),
[lead_code] [numeric](16, 0) NULL,
CONSTRAINT [PK_TBL1_TRAN_CODE] PRIMARY KEY
(
[TRAN_CODE] ,[TR_DATE]
)
)

CREATE TABLE [dbo].[TBL2](
[TRAN_CODE] [varchar](12) NOT NULL,
[TR_DATE] [datetime] NOT NULL CHECK ([TR_DATE] < '20060101'),
[lead_code] [numeric](16, 0) NULL,
CONSTRAINT [PK_TBL2_TRAN_CODE] PRIMARY KEY
(
[TRAN_CODE] ,[TR_DATE]
)
)

CREATE TABLE [dbo].[TBL3](
[TRAN_CODE] [varchar](12) NOT NULL,
[TR_DATE] [datetime] NOT NULL CHECK ([TR_DATE] < '20060101'),
[lead_code] [numeric](16, 0) NULL,
CONSTRAINT [PK_TBL3_TRAN_CODE] PRIMARY KEY
(
[TRAN_CODE] ,[TR_DATE]
)
)

---------------------------------------------

CREATE VIEW VIEWTBLALL
as

SELECT * from TBL1
union all
SELECT * from TBL2
union all
SELECT * from TBL3

-------------------------------

INSERT VIEWTBLALL( [TRAN_CODE],[TR_DATE],LEAD_CODE)

SELECT [TRAN_CODE],[TR_DATE],LEAD_CODE FROM <ORIGINAL_TABLE>
WHERE [TR_DATE] >= '20060101' and [TR_DATE] < '20080101'

----------------
it show below message

(201378 row(s) affected)

(16 row(s) affected)

---------------

why is it showing additional 16 rows affected ?

any help will be appreciated......thanx in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 04:22:21

Do you have a trigger?
Most probably is that not all records are inserted into same underlying table.
It seems 16 of them are inserted into another table than the 200k others.

N 56°04'39.26"
E 12°55'05.63"

VipinMitta
Starting Member

12 Posts

Posted - 2009-07-01 : 04:41:00

NO there is no trigger on any member tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources