How to Make Create or Replace View Work in SQL Server

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

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

SQL Server equivalent to Oracle's CREATE OR REPLACE VIEW

The solutions above though they will get the job done do so at the risk of dropping user permissions. I prefer to do my create or replace views or stored procedures as follows.

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_myView]'))
    EXEC sp_executesql N'CREATE VIEW [dbo].[vw_myView] AS SELECT ''This is a code stub which will be replaced by an Alter Statement'' as [code_stub]'
GO

ALTER VIEW [dbo].[vw_myView]
AS
SELECT 'This is a code which should be replaced by the real code for your view' as [real_code]
GO

CREATE OR REPLACE VIEW sql error

SQLite does not support the CREATE OR REPLACE syntax. The only database that I know which supports that syntax is Oracle, but I am guessing there are others.

Drop the view and create it with the new definition:

DROP VIEW IF EXISTS [vtable]; -- "OR REPLACE"
CREATE VIEW [vtable] AS SELECT * FROM Files_Table ORDER BY File;

Oracle SQL create or replace view with/without column names

Unfortunately Oracle checks the query text only and since it has not been changed, Oracle doesn't not really rebuild it.

Just modify a text a bit and all will be fine:

SQL> create or replace view v1(a,c,b) as select 'a' a,'b' b,'c' c from dual;

View created.

SQL> select * from v1;

A C B
- - -
a b c

SQL> create or replace view v1(a,b,c) as select 'a' a,'b' b,'c' c from dual;

View created.

SQL> select * from v1;

A C B
- - -
a b c

-- I've added just one extra space after dual:
SQL> create or replace view v1(a,b,c) as select 'a' a,'b' b,'c' c from dual ;

View created.

SQL> select * from v1;

A B C
- - -
a b c

PS. This bug exists on Oracle 19+ only. I've checked it on 11.2.0.4 and 18.5, and all is fine on them. It's better to register SR. Let me know if you want me register it

Incorrect syntax near 'REPLACE' with MSSQL

In SQL Server 2016 SP1 and later (including Azure SQL Database), use CREATE OR ALTER VIEW for the equivalent functionality. In earlier SQL Server versions, one must first drop the view and then CREATE VIEW and GRANT permissions.

how to CREATE and UPDATE VIEW in SQL Server 2008 using single query

What is necessary to do is add GO after first CREATE VIEW.

CREATE VIEW vw_delay AS
            SELECT ....
GO
  ALTER VIEW vw_RDoc AS
            SELECT ....

Then it will work.

creating multiple views in the same script in SQL Server

So the question is: hoy can I put everything in the same script, where I can create views to avoid doing a lot of subqueries, and without getting this errors?

There's no need to check for existence of the view. Just CREATE OR ALTER VIEW

CREATE OR ALTER VIEW NAME_OF_THE_VIEW
AS

-- Here I put the query of the view

SELECT *
FROM table_1

GO

CREATE OR ALTER VIEW NAME_OF_THE_OTHER_VIEW
AS

-- Here I put the query of the view

SELECT *
FROM table_1

---

Related Topics