This article explains how to convert most database objects from Oracle to MS SQL.
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
syscolumns that could help to extract information about table
structure, however this is not a recommended approach.
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');
Every specific Oracle function must be converted into MS SQL equivalent according to this table:
|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|
|locate(), instr(), position()||charindex()|
|string1 || string2||string1 + string2|
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
The main issue of converting Oracle triggers into MS SQL format is missing particular features in the target DBMS:
BEFORE INSERT/UPDATE/DELETEtrigger properties
FOR EACH ROWpattern
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.
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) some_column varchar(50) )