Inserting records through view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting records through view

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-08-10 : 11:10:24

Gurus,

While Inserting the records to view I am getting the following error.

/*
Msg 4406, Level 16, State 1, Line 2
Update or insert of view or function 'Vw_temp' failed because it contains a derived or constant field.
*/

Create Table Temp (Cola Int, Colb Varchar(20), Colc Datetime)
Create Table Temp_Part (Cola Int, Colb Varchar(20), Colc Datetime)

Create View Vw_temp as
Select * from temp
Union
Select * from Temp_Part

How can i resolve the problem. Am I doing any wrong way?
Please advice me to resolve this issue.

Thanks
Krishna

pootle_flump

1064 Posts

Posted - 2007-08-10 : 11:28:43

Hi Krishna - what are you actually trying to do here? Where do you want the record to go? I presume these are not partitioned...

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-08-10 : 11:33:32

Pootle, you are correct. Those tables are not partitioned. The Table Temp_Part containes 172 million of records. Now i want to do insert the records into new table. That is temp table. Is it possible to do like this.

Thanks for your response.
Krishna

pootle_flump

1064 Posts

Posted - 2007-08-11 : 06:38:00

So you want to populate Temp with the records in Temp_Part? If so - no this is not the way. Please confirm or correct my understanding and I'll sort out the SQL. If I am reading you right then it is trivial.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-08-13 : 02:15:56

My understanding is that through view you want to show records from both the table but when user is inserting the record, you want the record to be inserted in temp table.

If my understanding is correct then i suggest you to use Instead Of trigger.

Demostration with example:

Create Table Temp (Cola Int, Colb Varchar(20), Colc Datetime)
Create Table Temp_Part (Cola Int, Colb Varchar(20), Colc Datetime)

Create View Vw_temp as
Select * from temp
Union
Select * from Temp_Part

INSERT INTO temp VALUES (2,'bohra',getdate())

INSERT INTO temp_part VALUES (4,'bohra',getdate())

--Below insert statement will raise an error.
Insert into vw_temp
values(5,'pk_bohra',getdate())

CREATE TRIGGER trg_view_temp
ON vw_temp
instead OF insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO temp SELECT * FROM inserted
-- Insert statements for trigger here

END
GO

--Below statement will work
Insert into vw_temp
values(5,'pk_bohra',getdate())

--You can see the record in temp table
SELECT * FROM temp

If my understanding is wrong, then please correct me and explain more clearly about your requirement, so that i can help you.

Regards,
bohra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources