473,394 Members | 1,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Converting Oracle SQL to MS-SQLServer SQL

Hello,

Has anyone a small tool or somekind of document which could help me
to convert Oracle SQL scripts to SQL Server?

Scripts are not very Oracle specified.

Thanks,

Below is a Script that I would e.g convert to MS SQLServer:

SET SCAN ON

PROMPT Enter the password and TNS name.
PROMPT Enter the oracle SID for TNS name if you are running a local database.

CONNECT system/&systempassword@&&tnsname

-- Drop the user and all other related objects.
DROP USER webstore CASCADE;

-- Creating the schema
CREATE USER webstore IDENTIFIED BY welcome;

-- Grant the permissions to the user.
GRANT RESOURCE, CONNECT TO webstore;
ALTER USER webstore DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;

ALTER USER webstore TEMPORARY TABLESPACE temp;

CONNECT webstore/welcome@&&tnsname

PROMPT Creating Tables

-- Create the category table which contains the data for the categories.
CREATE TABLE category (
id NUMBER(10) CONSTRAINT pk_vsm_country PRIMARY KEY,
name VARCHAR2(20) NOT NULL
);

-- Create category attributes table which has attributes for the categories in
-- the category table.
CREATE TABLE category_attributes (
category_id NUMBER(10) NOT NULL,
label VARCHAR2(20) NOT NULL,
CONSTRAINT pk_vsm_cat_attr PRIMARY KEY(category_id,label),
CONSTRAINT rk_vsm_catattr_cat FOREIGN KEY(category_id) REFERENCES
category(id) ON DELETE CASCADE
);

-- Create country table to hold the country names.
CREATE TABLE country (
id NUMBER(4) CONSTRAINT pk_country PRIMARY KEY,
country_name VARCHAR2(50) CONSTRAINT uniq_country UNIQUE
);

-- Create users table to hold user details.
CREATE TABLE users (
user_name VARCHAR2(20) CONSTRAINT pk_users PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name VARCHAR2(20),
e_mail VARCHAR2(50) NOT NULL,
address VARCHAR2(200) NOT NULL,
city VARCHAR2(20) NOT NULL,
state VARCHAR2(20) NOT NULL,
country NUMBER(4) NOT NULL ,
zip NUMBER(8) NOT NULL,
phone VARCHAR2(20) NOT NULL,
role VARCHAR2(10) NOT NULL,
password VARCHAR2(200) NOT NULL,
card_provider VARCHAR2(30),
card_number VARCHAR2(200),
card_expiry_date DATE ,
CONSTRAINT rk_usr_cntry FOREIGN KEY(country) REFERENCES
country(id) ON DELETE CASCADE
);

-- Create shops master table.
CREATE TABLE shops (
id NUMBER(10) CONSTRAINT pk_shops PRIMARY KEY,
shop_name VARCHAR2(50) NOT NULL,
user_name VARCHAR2(20) NOT NULL,
description VARCHAR2(4000),
category_id NUMBER(10) NOT NULL,
reg_date DATE NOT NULL,
status VARCHAR2(20) NOT NULL,
CONSTRAINT chk_shops CHECK( status IN ('Approved', 'ApprovalPending',
'Rejected','Discontinued') ),
CONSTRAINT rk_shop_cat FOREIGN KEY (category_id) REFERENCES category(id)
ON DELETE CASCADE,
CONSTRAINT rk_shop_user FOREIGN KEY(user_name) REFERENCES users(user_name)
ON DELETE CASCADE
);

-- Create sub_category table which has the sub categories listed for each shop.
CREATE TABLE sub_category (
id NUMBER(10) CONSTRAINT pk_subcat PRIMARY KEY,
category_id NUMBER(10) NOT NULL,
shop_id NUMBER(10) NOT NULL,
name VARCHAR2(20) NOT NULL,
CONSTRAINT rk_subcat_cat FOREIGN KEY(category_id) REFERENCES category(id)
ON DELETE CASCADE,
CONSTRAINT rk_subcat_shop FOREIGN KEY(shop_id) REFERENCES shops(id)
ON DELETE CASCADE
);

-- Create item master table.
CREATE TABLE item (
id NUMBER(10) CONSTRAINT PK_VSM_ITEM PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
category_id NUMBER(10) NOT NULL,
shop_id NUMBER(10) NOT NULL,
description VARCHAR2(4000),
unit_price NUMBER(15,2) NOT NULL,
image VARCHAR2(50),
CONSTRAINT rk_item_subcat FOREIGN KEY (category_id ) REFERENCES
sub_category(ID) ON DELETE CASCADE ,
CONSTRAINT rk_item_shop FOREIGN KEY(shop_id) REFERENCES shops(id)
ON DELETE CASCADE
);

-- Create item_attributes table which has the item attributes for the items
CREATE TABLE item_attributes (
item_id NUMBER(10) NOT NULL,
label VARCHAR2(20) NOT NULL,
description VARCHAR2(4000),
CONSTRAINT pk_item_attr PRIMARY KEY(item_id , label),
CONSTRAINT rk_itemattr_item FOREIGN KEY(item_id) REFERENCES item(id)
ON DELETE CASCADE
);

-- Creat inventory table which maps the quantitly against the item.
CREATE TABLE inventory (
item_id NUMBER(10) CONSTRAINT pk_inventory PRIMARY KEY,
quantity NUMBER(4) NOT NULL,
CONSTRAINT RK_INVNTRY_ITEM FOREIGN KEY(item_id) REFERENCES item(id)
ON DELETE CASCADE
);

-- Create orders master table which stores all the completed orders.
CREATE TABLE orders (
id NUMBER(10) CONSTRAINT pk_orders PRIMARY KEY,
order_date DATE NOT NULL,
user_name VARCHAR2(20) NOT NULL,
shop_id NUMBER(10) NOT NULL,
ship_to_address VARCHAR2(100),
city VARCHAR2(20),
state VARCHAR2(20),
country NUMBER(4),
zip NUMBER(8),
phone VARCHAR2(20),
CONSTRAINT rk_ordr_usr FOREIGN KEY(user_name) REFERENCES users (user_name)
ON DELETE CASCADE,
CONSTRAINT rk_ordr_shop FOREIGN KEY(shop_id) REFERENCES shops(id)
ON DELETE CASCADE
);
-- Create order_items table to store the detailed orders
CREATE TABLE order_items (
order_id NUMBER(10) NOT NULL,
item_id NUMBER(10) NOT NULL,
quantity NUMBER(4) NOT NULL,
unit_price NUMBER(15,2) NOT NULL,
status VARCHAR2(20) NOT NULL,
CONSTRAINT chk_oi check ( Status in ('Pending','Shipped')),
CONSTRAINT rk_oi_ordr FOREIGN KEY(order_id) REFERENCES orders(id)
ON DELETE CASCADE ,
CONSTRAINT rk_oi_item FOREIGN KEY(item_id) REFERENCES item(id)
ON DELETE CASCADE
);

-- Create guest_book table to store all the guest_book entries
CREATE TABLE guest_book (
id NUMBER(10) CONSTRAINT pk_guest_book PRIMARY KEY,
user_name VARCHAR2(50) NOT NULL,
email_id VARCHAR2(50),
rating NUMBER(1),
comment_date DATE NOT NULL,
comments VARCHAR2(4000)
);

-- create sequences.
CREATE SEQUENCE category_seq MAXVALUE 9999999999;
CREATE SEQUENCE shops_seq MAXVALUE 9999999999;
CREATE SEQUENCE sub_category_seq MAXVALUE 9999999999;
CREATE SEQUENCE item_seq MAXVALUE 9999999999;
CREATE SEQUENCE inventory_seq MAXVALUE 9999999999;
CREATE SEQUENCE orders_seq MAXVALUE 9999999999;
CREATE SEQUENCE guest_book_seq MAXVALUE 9999999999;
Jul 20 '05 #1
1 9271

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Ton | last post by:
I have a question about connectivity between different databases. Oracle, DB2400 and SQL_server. It is easy to integrate application products that are running on different DB platforms? What...
2
by: Rosy Moss | last post by:
I am in the process of cleaning up a database that our company uses to track jobs, time and expense, and customer information. We are running Windows 2000 Server with approximately 20 terminals...
10
by: Andrea M. Segovia | last post by:
Hello, I am a newbie to Oracle databases.... We have a visualization front-end tool connected to an Oracle back-end database on a Tru64 UNIX server. We also have clients with MS access...
13
by: BigDaDDY | last post by:
Um yeah....In case you haven't figured it out, Microsoft sucks. I'm going to be kicked back in my chair eating popcorn and watching football 10 years from now, while all you clowns are scrambling...
11
by: Rosco | last post by:
Does anyone have a good URL or info whre Oracle and Access are compared to one another in performance, security, cost etc. Before you jump on me I know Oracle is a Cadillac compared to Access the...
7
by: D. Patrick | last post by:
I need to connect to an Oracle database. I've never done it before. I see that with framework 1.1 you had to download ODP.NET from Oracle's site in order for the framework classes to even work....
5
by: sparks | last post by:
We are slowly converting all of our older access 97 databases to 2003. One of them that has been running fine for 3 + years and has over 2000 records in is giving me a problem. It converted fine...
28
by: Randy Reimers | last post by:
(Hope I'm posting this correctly, otherwise - sorry!, don't know what else to do) I wrote a set of programs "many" years ago, running in a type of basic, called "Thoroughbred Basic", a type of...
2
by: iitt2007 | last post by:
I need to convert Oracle SQL SPs and functions to SQL 2005. Table and columns names do match. Is there any tool which will 'convert' Oracle script yntax to to SQL 2005 syntax? I can't install it on...
4
by: --CELKO-- | last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.