T-SQL

Re: update pe view, sql server 2005

Alex — Mon, 24 Oct 2011 12:54:14 GMT

multumesc mult, functioneaza (eu tot oncerca cu check constraint nu si primary kwy)

Re: update pe view, sql server 2005

rsocol — Tue, 18 Oct 2011 12:50:35 GMT

Vezi și secțiunea "Updatable views" din http://msdn.microsoft.com/en-us/library/ms187956(v=sql.90).aspx.

Răzvan

Re: update pe view, sql server 2005

rsocol — Tue, 18 Oct 2011 12:48:40 GMT

Asta merge:

create table TEST_HIST(COL1 int PRIMARY KEY CHECK (col1 BETWEEN 1 AND 4), COL2 varchar(20), COL3 datetime)
create table TEST_LIVE(COL1 INT PRIMARY KEY CHECK (col1>4), COL2 varchar(20), COL3 datetime)

INSERT INTO TEST_HIST SELECT 1,'test 1', '2011-10-10 13:04:14.403'
INSERT INTO TEST_HIST SELECT 2,'test 2', '2011-10-11 13:04:14.403'
INSERT INTO TEST_HIST SELECT 3,'test 3', '2011-10-12 13:04:14.403'
INSERT INTO TEST_HIST SELECT 4,'test 4', '2011-10-13 13:04:14.403'

INSERT INTO TEST_LIVE SELECT 5,'test 5', '2011-10-15 13:04:14.403'
INSERT INTO TEST_LIVE SELECT 6,'test 6', '2011-10-16 13:04:14.403'
INSERT INTO TEST_LIVE SELECT 7,'test 7', '2011-10-17 13:04:14.403'
INSERT INTO TEST_LIVE SELECT 8,'test 8', '2011-10-18 13:04:14.403'
INSERT INTO TEST_LIVE SELECT 9,'test 9', '2011-10-19 13:04:14.403'

go
CREATE VIEW dbo.VW_TEST
AS
SELECT * FROM dbo.TEST_LIVE
UNION ALL
SELECT * FROM dbo.TEST_HIST

go
update a
SET a.COL2 ='test x'
from VW_TEST a where a.COL1 = 1

Vezi Partitioned Views in MSDN.

Razvan

update pe view, sql server 2005

Alex — Tue, 18 Oct 2011 11:51:42 GMT

Salut, cum trebuie defapt sa fie?

drop table TEST_LIVE
drop table TEST_HIST
drop view VW_TEST

create table TEST_LIVE(COL1 int, COL2 varchar(20), COL3 datetime)
create table TEST_HIST(COL1 int, COL2 varchar(20), COL3 datetime)

INSERT INTO TEST_HIST SELECT 1,'test 1', '2011-10-10 13:04:14.403'
INSERT INTO TEST_HIST SELECT 2,'test 2', '2011-10-11 13:04:14.403'
INSERT INTO TEST_HIST SELECT 3,'test 3', '2011-10-12 13:04:14.403'
INSERT INTO TEST_HIST SELECT 4,'test 4', '2011-10-13 13:04:14.403'

INSERT INTO TEST_LIVE SELECT 5,'test 5', '2011-10-15 13:04:14.403'
INSERT INTO TEST_LIVE SELECT 6,'test 6', '2011-10-16 13:04:14.403'
INSERT INTO TEST_LIVE SELECT 7,'test 7', '2011-10-17 13:04:14.403'
INSERT INTO TEST_LIVE SELECT 8,'test 8', '2011-10-18 13:04:14.403'
INSERT INTO TEST_LIVE SELECT 9,'test 9', '2011-10-19 13:04:14.403'

alter VIEW dbo.VW_TEST
AS
SELECT * FROM dbo.TEST_LIVE
UNION
SELECT * FROM dbo.TEST_HIST

update a
SET a.COL2 ='test x'
from VW_TEST a where a.COL1 = 1

si primesc eroarea:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'a' failed because it contains a derived or constant field.

nu se poate face update-ul daca am union in view?