How to make CREATE OR REPLACE VIEW work in SQL Ser

问题:

This question already has answers here:

SQL Server equivalent to Oracle's CREATE OR REPLACE VIEW (9 answers)

Closed 3 years ago.

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?

回答1:

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

回答2:

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

回答3:

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.

回答4:

SQL Server 2016 Answer

With SQL Server 2016 you can now do (MSDN Source):

DROP VIEW IF EXISTS dbo.MyView

回答5:

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')

回答6:

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 & 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

回答7:

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

回答8:

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