How to migrate database from Oracle to SQL Server

This article explains how to convert most database objects from Oracle to MS SQL.

Table Definition

Oracle expose table definition through DESC tablename statement. In SQL Server you can use the system stored procedure sp_help for detailed information about a table's columns and other properties.

If output of this procedure is not suitable for your requirements, you could query the system view INFORMATION_SCHEMA.COLUMNS, to get the desired information. You could wrap your code inside a stored procedure named DESCRIBE, if you wish.

Finally, there are system tables like sysobjects and syscolumns that could help to extract information about table structure, however this is not a recommended approach.

Table DUAL

Oracle has special one-row and one-column table DUAL to run some queries that does not require any table, for example:

SELECT 1 FROM DUAL;

SQL Server does not have such table, so it must be created for possible use in queries:

create table dual (dummy varchar(1) not null);
insert into dual(dummy) values('x');

Specific functions

Every specific Oracle function must be converted into MS SQL equivalent according to this table:

Oracle SQL Server
current_date, current_time, current_timestamp getdate()
decode() no direct equivalent, see conversion below
extract() datepart()
greatest() no direct equivalent, see custom code below
least() no direct equivalent, see custom code below
sysdate, now() gedate()
locate(), instr(), position() charindex()
string1 || string2 string1 + string2

Decode() function

This is one of Oracle functions that do not have direct equivalent in MS SQL. If must be replaced by CASE expression as follows:

SELECT colorid, 
CASE colorid
WHEN '1' THEN 'white' 
WHEN '2' THEN 'black' 
WHEN '3' THEN 'red' 
ELSE NULL 
END AS 'colorname'
FROM colors

Greatest() and least() functions

This is how greatest() and least() functions could be emulated in SQL Server:

SELECT Greatest=MAX(col), Least=MIN(col)
FROM table_name
CROSS APPLY (
    SELECT col1 UNION ALL SELECT col2 UNION ALL SELECT col3
    UNION ALL SELECT col4) a(col)
GROUP BY primary_key

Triggers

The main issue of converting Oracle triggers into MS SQL format is missing particular features in the target DBMS:

The task is to implement missing capabilities by other means of MS SQL. For example, the goal of BEFORE-triggers is to update record affected by the last operation before inserting/updating it into the database. FOR EACH ROW pattern allows to apply the trigger to all rows affected by the last insert, update or delete operation. SQL Server triggers can process affected records in the database after insert or update operation completes. All modified records are collected in service tables "inserted" (for insert/update operation) or "deleted" (for delete operation).

Sequences

There is no support for sequences in MS SQL, the DBMS provides special property for numeric data types (tinyint, smallint, int, bigint, decimal, numeric) called "IDENTITY" instead:

CREATE TABLE mytable
(
id int IDENTITY(1, 1)
some_column varchar(50)
)