A question about bulk insert and partitioned views

To all,
I posted this question in the server list and that may not have been the
appropriate place so I am posting here.
I have built a partitioned view exactly as described in BOL. I can insert
data into just fine and it places the values in the correct sub-tables
according to the check constraint.
I would like to do bulk inserts on this partitioned view but I get an error
message stating that this view cannot be a target of a bulk operation. I
did include the CHECK_CONTRAINTS setting to the bulk insert call.
My question is are bulk inserts allowed into partitioned views (I cannot
find anything in BOL that states they cannot) or am I missing something in
setting up the view?
Thanks.
--seanCreate an "instead of" trigger on your view and use option FIRE_TRIGGERS wit
h
BULK INSERT.
Example:
use northwind
go
exec master..xp_cmdshell 'bcp "select orderid, convert(char(8), orderdate,
112) from northwind.dbo.orders where orderdate >= ''19970101'' and orderdate
< ''19990101''" queryout "c:\temp\test.bcp" -S server_name -T -c'
go
create table dbo.t1 (
colA int not null primary key check (colA between 10400 and 10807),
colB datetime not null
)
go
create table dbo.t2 (
colA int not null primary key check (colA between 10808 and 11077),
colB datetime not null
)
go
create view dbo.vw_myview
as
select colA, colB from dbo.t1
union all
select colA, colB from dbo.t2
go
create trigger tr_vw_myview_io_ins on dbo.vw_myview
instead of insert
as
insert into dbo.t1
select * from inserted where colA between 10400 and 10807
insert into dbo.t2
select * from inserted where colA between 10808 and 11077
go
bulk insert northwind.dbo.[vw_myview]
from 'c:\temp\test.bcp'
with
(
fieldterminator = '\t',
rowterminator = '\n',
fire_triggers
)
go
select * from dbo.vw_myview where colA = 10810
go
drop view dbo.vw_myview
go
drop table dbo.t1, dbo.t2
go
AMB
"Sean Shanny" wrote:

> To all,
> I posted this question in the server list and that may not have been the
> appropriate place so I am posting here.
> I have built a partitioned view exactly as described in BOL. I can insert
> data into just fine and it places the values in the correct sub-tables
> according to the check constraint.
> I would like to do bulk inserts on this partitioned view but I get an erro
r
> message stating that this view cannot be a target of a bulk operation. I
> did include the CHECK_CONTRAINTS setting to the bulk insert call.
> My question is are bulk inserts allowed into partitioned views (I cannot
> find anything in BOL that states they cannot) or am I missing something in
> setting up the view?
> Thanks.
> --sean
>
>