• Contact Us
  • About Us
Trending now

Secure Solar Connections: Why MC4 and Power…

How to Safely Break Adderall Addiction with…

Find Out How Bariatric Surgery Can Help…

From Gentle to Kinky: Porn Games That…

The #1 HVAC Tech School in Texas

Exploring Effective Strategies for Securing Agricultural Business…

Expert Water Heater Repair Services – Reliable…

Eco-Friendly Penetrating Oils: Pioneering Sustainable Solutions in…

Expert Windshield Replacement in Houston, TX –…

What Makes Healthcare SEO Unique? Understanding Its…

Create With Driven

  • Fashion
  • Auto
  • Finance
  • Tech
  • Home
Create With Driven
  • Home
  • Tech
  • Oracle to SQL Server Database Migration
Tech

Oracle to SQL Server Database Migration

by John HartmanMarch 2, 20230660
Share0

This whitepaper explores some tips of database migration from Oracle to MS SQL or its cloud variations such as Azure SQL.

Table Description

Oracle present table definition by using “DESC tablename” statement. In SQL Server feel free to make use of the system stored procedure sp_help for comprehensive information about a table’s columns and other properties.

If the end result is not appropriate for your requirements, then querying the system view with INFORMATION_SCHEMA.COLUMNS, will ensure you get the right information. One can also easily cover up ones code inside the stored system known as DESCRIBE, if you desire.

For the last process, you can query the system tables just like sysobjects and syscolumns, however this is not an advisable technique.

Table DUAL

When it comes to Oracle, it presents a unique one-row and one-column table that are DUAL to run a few queries that doesn’t require any table, for instance:

SELECT SYSDATE FROM DUAL;

Since MS SQL does not have the same table, it must be created during the migration for using in views and queries:

create table dual (varchar(1) not null );

insert into dual(dummy) values(‘x’);

Specific functions

Each and every specific Oracle function need to be transformed into MS SQL equivalent. However, some of them don’t have direct synonym. One of them is decode() function, it must be exchanged by CASE expression below:

SELECT id,

CASE id

WHEN ‘1’ THEN ‘one’

WHEN ‘2’ THEN ‘two’

WHEN ‘3’ THEN ‘three’

ELSE NULL

END AS ‘id_text’

FROM products

Greatest() and least() Oracle functions also do not have straight equivalent in MS SQL. These functions could be emulated in SQL Server as follows:

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 primary obstacle of transforming Oracle triggers into MS SQL format is missing particular features in the target DBMS: BEFORE INSERT/UPDATE/DELETE triggers and FOR EACH ROW pattern.

The process requires the implementation of some missing features by other method of MS SQL. For instance, semantic of BEFORE-triggers is used to modify record that are affected by the last process prior to inserting/updating it into the database. “FOR EACH ROW” structure let you implement the trigger to all rows influenced by the last insert, update or delete operation. SQL Server triggers can manage altered records in the database the moment the insert or update operation concludes. All improved records are gathered in service tables “inserted” (for insert/update operation) or “deleted” (for delete operation).

Sequences

You cannot find any support for sequences in MS SQL, the DBMS offers special attribute for numeric data types such as tinyint, smallint, int, bigint, decimal and numeric called “IDENTITY” instead:

CREATE TABLE mytable

(

id int IDENTITY(1, 1)

some_column varchar(50)

)

Share0
previous post
Most Auto Makers Plans To Switch To EVs By 2030
next post
Hand Washing: Why It’s So Important
John Hartman

Related posts

Breaking Down the Benefits of B2B Databases: A Comprehensive Guide

Clare LouiseMarch 14, 2023

Impact of buying Instagram followers on your engagement

John HartmanJuly 8, 2023October 15, 2023

Essential features you must know before you buy noise-cancelling earphones.

John HartmanFebruary 9, 2024February 14, 2024

Social Media

Categories

  • Architect (1)
  • Art (1)
  • Auto (33)
  • Basement Finish (1)
  • Business (165)
  • Casino (51)
  • Childcare (1)
  • Construction (1)
  • Dating (16)
  • Education (15)
  • Entertainment (6)
  • Environment (1)
  • Fashion (28)
  • Featured (45)
  • Finance (25)
  • Food (16)
  • Game (2)
  • Gift (1)
  • Health (100)
  • Home (97)
  • Home Improvements (14)
  • HVAC (2)
  • Industrial (3)
  • Insurance (3)
  • Law (9)
  • Lifestyle (7)
  • Marketing (2)
  • Mortgage (2)
  • Music (4)
  • Photography (4)
  • Podiatrist (1)
  • Real Estate (5)
  • Renewable Energy (1)
  • SEO (3)
  • Shopping (19)
  • Skin Care (1)
  • Sports (3)
  • Swimming pool (1)
  • Tech (52)
  • Travel (13)
  • Weight Loss (5)
  • Weight Loss (2)
  • Wellness program (1)

Recent Posts

Secure Solar Connections: Why MC4 and Power Entry Connectors Are Essential for System Stability

Ira ScalzoMay 14, 2025
May 14, 20250

How to Safely Break Adderall Addiction with Help from Houston Experts

Willie FerreriMay 5, 2025May 5, 2025
May 5, 2025May 5, 20250

Find Out How Bariatric Surgery Can Help Control Sleep Apnea

John HartmanMay 3, 2025
May 3, 20250

From Gentle to Kinky: Porn Games That Fulfill Every Desire

Willie FerreriMay 1, 2025May 6, 2025
May 1, 2025May 6, 20250

The #1 HVAC Tech School in Texas

William RobertsApril 26, 2025
April 26, 20250
Copyright © 2024 createwithdriven.com.
  • Contact Us
  • About Us
Create With Driven
FacebookTwitterInstagramYoutubeSnapchat
  • Fashion
  • Auto
  • Finance
  • Tech
  • Home