CODEWITHSUNDEEP
sqlsql-serveroracle-databasereplaceliquibase
Mad Scientist
Mad Scientist

Reputation: 917

Incorrect syntax near 'REPLACE' with MSSQL

I have migrated an oracle database to Microsoft SQL Server via liquibase but there are still some SQL statements that don't work. This one looked like this in oracle:

CREATE OR REPLACE VIEW "BP_RESULTS_VIEW" (
BP_ID,                                     
RES_NAME,                                     
RES_LONG_NAME,                                     
MEDIAN,                                    
LOW_HINGE,                                 
HIGH_HINGE,                                
H_SPREAD,                                  
INNER_FENCE_LOW,                           
INNER_FENCE_HIGH,
OUTER_FENCE_LOW,
OUTER_FENCE_HIGH,
LOW_NOTCH,
HIGH_NOTCH,
LOW_ADJACENT_VALUE,
HIGH_ADJACENT_VALUE)
  AS 
SELECT 
r.BP_ID,                                     
rv.RES_NAME,                                     
rv.RES_LONG_NAME,                                     
r.MEDIAN,                                    
r.LOW_HINGE,                                 
r.HIGH_HINGE,                                
r.H_SPREAD,                                  
r.INNER_FENCE_LOW,                           
r.INNER_FENCE_HIGH,
r.OUTER_FENCE_LOW,
r.OUTER_FENCE_HIGH,
r.LOW_NOTCH,
r.HIGH_NOTCH,
r.LOW_ADJACENT_VALUE,
r.HIGH_ADJACENT_VALUE
FROM 
bp_results r, 
results_view_display rv 
WHERE 
CAST (rv.value AS INT) = r.bp_id AND 
rv.type = 'BOX';

After migrating it to Microsoft SQL Server it looks like this:

CREATE OR REPLACE FORCE VIEW BP_RESULTS_VIEW (BP_ID, RES_NAME, RES_LONG_NAME, MEDIAN,
LOW_HINGE, HIGH_HINGE, H_SPREAD, INNER_FENCE_LOW, INNER_FENCE_HIGH,
OUTER_FENCE_LOW, OUTER_FENCE_HIGH, LOW_NOTCH, HIGH_NOTCH, 
LOW_ADJACENT_VALUE, HIGH_ADJACENT_VALUE) AS SELECT 
r.BP_ID,                                     
rv.RES_NAME,                                     
rv.RES_LONG_NAME,                                     
r.MEDIAN,                                    
r.LOW_HINGE,                                 
r.HIGH_HINGE,                                
r.H_SPREAD,                                  
r.INNER_FENCE_LOW,                           
r.INNER_FENCE_HIGH,
r.OUTER_FENCE_LOW,
r.OUTER_FENCE_HIGH,
r.LOW_NOTCH,
r.HIGH_NOTCH,
r.LOW_ADJACENT_VALUE,
r.HIGH_ADJACENT_VALUE
FROM 
bp_results r, 
results_view_display rv 
WHERE 
CAST (rv.value AS INT) = r.bp_id AND 
rv.type = 'BOX'
GO

But when I want to execute it always this error occurs:

Incorrect syntax near 'REPLACE'.

I don't understand why because the REPLACE statement exists in SQL Server too. It also seems like it doesn't recognize the CAST command. I am using Microsoft SQL Server Management Studio 17

Upvotes: 1

Views: 4678

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

SQL Server doesn't support CREATE OR REPLACE VIEW.

Instead, create the view the first time. Then simply use ALTER VIEW. That is the simplest method. You can also drop the view and the re-create it.

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46193

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.

Upvotes: 4

Related Questions