Hi @Miraj 

Try the following

IF OBJECT_ID('dbo.request_temp_view', 'V') IS NOT NULL
DROP VIEW dbo.request_temp_view
GO

CREATE VIEW request_temp_view AS
SELECT * FROM REQUEST

Cheers!

@Shneor Cheshin 
 

Hey @Miraj 

Run each script seperatly.

To make it simple

DROP VIEW request_temp_view

Then

CREATE VIEW request_temp_view AS
SELECT * FROM REQUEST

Cheers!

@Shneor Cheshin 

Thanks. yes, running separately works. although, I am trying to use a “create or alter” type of statement to get this to work in one go. To give some context, when we create views and the corresponding metadata for it, we can save the view script as following
 

So when we deliver to another environment, if the sql against this Table Script is present it automatically creates the view. The issue I have is when we make changes to an existing view and update the table script with a new sql. Now, “Create VIEW” no longer works becasue the target environment already has the view, and thus our changes don’t get automatically deployed upon delivery.

This is why I was exploring the option of using “CREATE OR ALTER” so that it would create if the view is not there or alter it if it were there.

Hey @Miraj 

I think you are looking for create or replace

CREATE OR REPLACE VIEW request_temp_view  AS
SELECT * FROM REQUEST

Cheers!

Hi @Miraj 

Try out this query on your SQL query tool so that the same query must work on the package deployment.

IF OBJECT_ID('c_data_view') IS NULL
BEGIN
EXEC('CREATE VIEW c_data_view AS SELECT TOP 10 * FROM REQUEST')
END
ELSE
BEGIN
EXEC('ALTER VIEW c_data_view AS SELECT TOP 10 * FROM REQUEST')
END

make sure the package deploying user has the EXECUTEDBQUERY, EXECUTEDBEDIT functions enabled under the role

IF OBJECT_ID('c_data_view') IS NULL
BEGIN
EXEC('CREATE VIEW c_data_view AS SELECT TOP 10 * FROM REQUEST')
END
ELSE
BEGIN
EXEC('ALTER VIEW c_data_view AS SELECT TOP 10 * FROM REQUEST')
END

Thanks mate. This works