Partitioning column issue

• luckysql.kinda

  SSCertifiable

  Points: 7816

  More actions

  December 16, 2009 at 8:50 pm

  #213612

  In the following query, my last query is not working. The error is given as follows:

  IF EXISTS(SELECT * FROM sysobjects where name = 'ARCTESTEMP')

  BEGIN

  DROP TABLE ARCTESTEMP

  END

  IF EXISTS(SELECT * FROM sysobjects where name = 'TESTEMP')

  BEGIN

  DROP TABLE TESTEMP

  END

  IF EXISTS(SELECT * FROM sysobjects where name = 'VIEW_EMP')

  BEGIN

  DROP VIEW VIEW_EMP

  END

  Create table testEmp(Empid INT PRIMARY KEY CHECK (empid <10), Empname Varchar(20) NOT NULL)

  insert into testEmp values(1,'Ashish')

  insert into testEmp values(2,'Manish')

  insert into testEmp values(3,'Shweta')

  insert into testEmp values(4,'Kiran')

  insert into testEmp values(5,'Prabha')

  Create table ArctestEmp(Empid INT PRIMARY KEY CHECK (empid <10), Empname Varchar(20) NOT NULL)

  insert into ArctestEmp

  select empid, empname from testEmp where Empid > 3

  delete from testEmp where Empid > 3

  select * from ArctestEmp

  select * from testEmp

  create view View_Emp

  AS

  Select * from testEmp

  UNION ALL

  Select * from ArctestEmp

  Following query gives error 🙁

  DELETE FROM View_Emp WHERE EMPID = 2

  Msg 4436, Level 16, State 12, Line 1

  UNION ALL view 'TestDB.dbo.View_Emp' is not updatable because a partitioning column was not found.

• Tommy Bollhofer

  SSChampion

  Points: 14940

  More actions

  December 17, 2009 at 7:26 am

  #1093482

  Refer to Modifying Data Through a View in BOL -

  http://technet.microsoft.com/en-us/library/ms180800.aspx

  Tommy

  Follow @sqlscribe