Re: seeing views in wizard

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 03/16/04 

Date: Tue, 16 Mar 2004 07:13:33 -0000

Ok using the wizard OR the designer should be the same so

The problem with views as destinations is that you can only insert into 1
base table. If the view comprises > 1 table then specifying the view as a
whole will not work

The views are not listed in the dropdown but you can cheat and use
disconnected edit to set it for you.

Just remember this rule though

Say I have this structure

CREATE TABLE t1(id int primary key not null, descr varchar(200))
CREATE TABLE t2(id int primary key not null, t1ref int references t1(id))

--Create a view over the top
create view v1
as
select t1.id as id1,t1.descr, t2.id as id2, t2.t1ref from t1 join t2 on
t1.id = t2.t1ref

--If I do this
insert v1
values(1,'Allan',1,1)

--I get this
Server: Msg 4405, Level 16, State 2, Line 1
View or function 'v1' is not updatable because the modification affects
multiple base tables.

--But if I do this
insert v1(id1, descr)
values(1,'Allan')

--it works 

-- 
----------------------------
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alison" <anonymous@discussions.microsoft.com> wrote in message
news:ab8901c40ad5$434cb970$a601280a@phx.gbl...
> Hello all,
> I think this is a simple question, should I be able to see
> the views lited in my dropdown destination list of tables
> when importing data. If so is there any simple reasons why
> I may not be able to do so?
> thanks
> alison

Relevant Pages

  • Re: Import Text into Multiple tables
    ... No he read it as 20 source definitions and 20 seperate destinations. ... it as all the 20 destinations are of differing structures. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ...
    (microsoft.public.sqlserver.dts)
  • Data insertion too too slow...
    ... Env is ms sql server 2000. ... create table srchPool(tid int primary key, taid int, s tynyint, uid ... from targetTBL ...
    (comp.databases.ms-sqlserver)
  • Re: Need help on a select statement using MYSQL ^.^
    ... > person_id int primary key ... > mood ... Well "business problem" may not be the right world, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: subquery problem, wrong reference in the subquery?
    ... Columnist, SQL Server Professional ... The query still runs and give out results. ... create table t2 (rowid int primary key, rowname varchar(100)) ...
    (microsoft.public.sqlserver.programming)
  • Re: Compare Insert vs Update
    ... Columnist, SQL Server Professional ... On compairing Insert vs Update, which statement causes more overhead to the ... If i have a Table(col1 int primary key) ...
    (microsoft.public.sqlserver.programming)