This informative article describes the best way to convert a lot of database objects from Oracle to MS SQL.
Table Definition
Oracle reveal table definition by means of DESC tablename statement. In SQL Server you need to use the program stored technique sp_help for more information about a table’s columns and other properties.
If the output of this process isn’t appropriate for your needs, you can query the program view INFORMATION_SCHEMA.COLUMNS, to achieve the required information. One could encapsulate their code within the stored procedure named DESCRIBE, if you desire.
Lastly, you will find program tables like sysobjects and syscolumns that can make it possible to extract details about table structure, nevertheless this is not really a highly recommended technique.
Table DUAL
Oracle has special one-row and one-column table DUAL to perform some queries that doesn’t call for 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
It’s one of Oracle functions that don’t have direct equivalent in MS SQL. If it needs to be substituted with CASE expression, below are the process:
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:
- SQL Server does not support BEFORE INSERT/UPDATE/DELETE trigger properties
- SQL Server does not support FOR EACH ROW pattern
The process will be to apply missing features by other methods of MS SQL. For instance, the purpose of BEFORE-triggers is to update record subject to the previous procedure 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)
)
