473,326 Members | 2,048 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,326 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 9265

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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.