March 12, 2009 at 9:16 am
Hi,
At present I dont have Sql Server, so cant test it out.
Kindly clarify some doubts.
I have a table named A which has fields emp, empno, empaddress. Here empno is a primary key.
I am going to create a view V, which will fetch only emp and empno.
My question is can i insert datas into V, and will it affect the table A.
Kindly excuse me, as i dont have sql server I could not try out this simple question.
Thanks in Advance
March 12, 2009 at 9:24 am
yes.
whether the PK is identity, or a non identity PK:
both of these examples work:
[font="Courier New"]
CREATE TABLE A( empno INT IDENTITY(1,1) PRIMARY KEY, emp VARCHAR(64),empaddress VARCHAR(64))
GO
CREATE VIEW V AS SELECT empno, emp FROM A
GO
INSERT INTO V(emp)
SELECT 'bob' UNION ALL
SELECT 'jeff'
DROP TABLE A
DROP VIEW V
GO
CREATE TABLE A( empno INT PRIMARY KEY, emp VARCHAR(64),empaddress VARCHAR(64))
GO
CREATE VIEW V AS SELECT empno, emp FROM A
GO
INSERT INTO V(empno,emp)
SELECT 1,'bob' UNION ALL
SELECT 42,'jeff'[/font]
Lowell
March 12, 2009 at 9:29 am
But if I insert into V, will it be inserted into A too
March 12, 2009 at 9:41 am
Yes. Views don't really store data on their own. They're just a way to connect to the table(s) they query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2009 at 9:46 am
GSquared thanks for the reply, but can you be more specific.
You mean to say that
If I have a table A, that has a primary key which is not being referenced to any other table, and I have a view V created on A.
If I insert values into V, will it be inserted into table A
right?
March 12, 2009 at 9:51 am
Hemalatha (3/12/2009)
GSquared thanks for the reply, but can you be more specific.You mean to say that
If I have a table A, that has a primary key which is not being referenced to any other table, and I have a view V created on A.
If I insert values into V, will it be inserted into table A
right?
Exactly.
When you insert into a view, you aren't actually inserting into the view. SQL is just using the view as a connection to insert into the table. Same for deleting, updating, and even selecting. The view is just a way to tell SQL which table or tables you really mean. All the actual work takes place in the table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2009 at 9:52 am
Hemalatha (3/12/2009)
If I have a table A, that has a primary key which is not being referenced to any other table, and I have a view V created on A.If I insert values into V, will it be inserted into table A
A view is just a saved select statement. It doesn't store anything. Hence, if you insert into a view there are only two options.
1) The data is inserted into the table that the view is based on
2) SQL throws an error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply