How to make CREATE OR REPLACE VIEW work in SQL Server

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

User · Answer

The accepted solution has an issue with the need to maintain the same statement twice, it isnt very efficient (although it works). In theory Gordon Linoff's solution would be the go, except it does not work in MSSQL because create view must be the first line in a batch.

The drop/create does not answer the question as posed. The following does the job as per the original question.

if not exists (select * from sysobjects where name='TABLE_A' and xtype='V')
exec ('create view SELECT 
VCV.xxxx,
    VCV.yyyy AS yyyy,
    VCV.zzzz AS zzzz
FROM TABLE_A')

User · Answer

Altering a view could be accomplished by dropping the view and recreating it  Use the following to drop and recreate your view   IF EXISTS  SELECT NAME FROM SYS VIEWS WHERE NAME    dbo test abc def   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

User · Answer

SQL Server 2016 Answer  With SQL Server 2016 you can now do  MSDN Source    DROP VIEW IF EXISTS dbo MyView

User · Answer

Edit   Although this question has been marked as a duplicate  it has still been getting attention   The answer provided by  JaKXz is correct and should be the accepted answer      You ll need to check for the existence of the view   Then do a CREATE VIEW or ALTER VIEW depending on the result   IF OBJECT ID  dbo data VVVV   IS NULL BEGIN     CREATE VIEW dbo data VVVV     AS     SELECT VCV xxxx  VCV yyyy AS yyyy  VCV zzzz AS zzzz FROM TABLE A VCV END ELSE     ALTER VIEW dbo data VVVV     AS     SELECT VCV xxxx  VCV yyyy AS yyyy  VCV zzzz AS zzzz FROM TABLE A VCV BEGIN END

User · Answer

IF NOT EXISTS select   FROM sys views where name    data VVVV        BEGIN         CREATE VIEW data VVVV AS          SELECT VCV xxxx  VCV yyyy AS yyyy  VCV zzzz AS zzzz FROM TABLE A VCV     END ELSE     BEGIN         ALTER VIEW data VVVV AS          SELECT VCV xxxx  VCV yyyy AS yyyy  VCV zzzz AS zzzz FROM TABLE A VCV     END

User · Answer

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

User · Answer

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

User · Answer

How about something like this  comments should explain   --DJ - 2015-07-15 Example for view CREATE or REPLACE  --Replace with schema and view names DECLARE  viewName NVARCHAR 30    T   DECLARE  schemaName NVARCHAR 30    dbo    --Leave this section as-is BEGIN TRY     DECLARE  view AS NVARCHAR 100      CREATE VIEW      schemaName          viewName     AS SELECT      AS  1        EXEC sp executesql          view  END TRY BEGIN CATCH     PRINT  View already exists   END CATCH  GO  --Put full select statement here after modifying the view  amp  schema name appropriately ALTER VIEW  dbo   T  AS     SELECT     AS  2   GO   --Verify results with select statement against the view SELECT    FROM     T     Cheers -DJ

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

Examples related to sql

Examples related to sql-server

Examples related to view

Examples related to porting