delete from table then insert - using a view

Question

delete from table then insert - using a view

Minnu

SSCertifiable

Points: 6451
More actions
November 24, 2015 at 3:20 am

#302540

hi Team,
How to create a view with below logic,
-- want to delete from unify table before insert.
DELETE FROM dbo.Unify
GO
INSERT INTO dbo.unify (PS_code, PS_Name)
SELECT PS_code, PS_Name FROM [dbo].[Unify_HHT]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

Minnu (11/24/2015)
hi Team,
How to create a view with below logic,
-- want to delete from unify table before insert.
DELETE FROM dbo.Unify
GO
INSERT INTO dbo.unify (PS_code, PS_Name)
SELECT PS_code, PS_Name FROM [dbo].[Unify_HHT]

Views do not contain DELETEs and INSERTs, merely SELECTs.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BrainDonor SSCoach Points: 19254 More actions · Answer 2

You can delete or insert data by referencing a view, but the view itself only contains SELECT.

https://msdn.microsoft.com/en-GB/library/ms180800(v=sql.105).aspx

Steve Hall
Linkedin
Blog Site

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 3

Well, in the spirit of answering the question asked, it'd be pretty easy to create a view that looks the same as the table with schemabinding, and put an INSTEAD OF (insert,update,delete) trigger on it that does what you want. I'll leave it for you to google SQL SERVER INSTEAD OF if that's really what you want to do but why not this instead?

INSERT INTO dbo.unify (PS_code, PS_Name)

SELECT PS_code, PS_Name FROM [dbo].[Unify_HHT] a

where not exists

( select 1 from dbo.unify b where b.ps_code = a.ps_code and b.ps_name = a.ps_name)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Minnu (11/24/2015)
hi Team,
How to create a view with below logic,
-- want to delete from unify table before insert.
DELETE FROM dbo.Unify
GO
INSERT INTO dbo.unify (PS_code, PS_Name)
SELECT PS_code, PS_Name FROM [dbo].[Unify_HHT]

As others have pointed out, a view is the wrong object type for this. There's a great reminder in the name "view". That's all you get to do. The correct object type for this operation is not a view, but a stored procedure. They're for doing stuff, not just looking at stuff.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden