How to make CREATE OR REPLACE VIEW work in SQL Server? [duplicate]

Question

CREATE OR REPLACE VIEW doesn't seem to work in SQL Server. So how do I port CREATE OR REPLACE VIEW to work on SQL Server?

This is what I'm trying to do:

CREATE OR REPLACE VIEW data_VVVV AS  SELECT      VCV.xxxx,         VCV.yyyy AS yyyy,         VCV.zzzz AS zzzz FROM  TABLE_A ; 

Any ideas?

Answer 1

Borrowing from @Khan's answer, I would do:

IF OBJECT_ID('dbo.test_abc_def', 'V') IS NOT NULL     DROP VIEW dbo.test_abc_def GO  CREATE VIEW dbo.test_abc_def AS SELECT      VCV.xxxx     ,VCV.yyyy AS yyyy     ,VCV.zzzz AS zzzz FROM TABLE_A 

MSDN Reference

Answer 2

Here is another method, where you don't have to duplicate the contents of the view:

IF (NOT EXISTS (SELECT 1 FROM sys.views WHERE name = 'data_VVV')) BEGIN     EXECUTE('CREATE VIEW data_VVVV as SELECT 1 as t'); END;  GO  ALTER VIEW data_VVVV AS      SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A ; 

The first checks for the existence of the view (there are other ways to do this). If it doesn't exist, then create it with something simple and dumb. If it does, then just move on to the alter view statement.