This informative article describes the best way to convert a lot of database objects from Oracle to MS SQL.
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.
Oracle has special one-row and one-column table DUAL to perform some queries that doesn’t call for any table, for example:
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’);
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
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
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:
WHEN ‘1’ THEN ‘white’
WHEN ‘2’ THEN ‘black’
WHEN ‘3’ THEN ‘red’
END AS ‘colorname’
Greatest() and least() functions
This is how greatest() and least() functions could be emulated in SQL Server:
SELECT Greatest=MAX(col), Least=MIN(col)
CROSS APPLY (
SELECT col1 UNION ALL SELECT col2 UNION ALL SELECT col3
UNION ALL SELECT col4) a(col)
GROUP BY primary_key
The main issue of converting Oracle triggers into MS SQL format is missing particular features in the target DBMS:
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).
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)
Jul 11, 2017 0
Jul 01, 2017 0
Jul 28, 2018 0