Converting Oracle SQL to MS-SQLServer SQL

[Mike Landis]

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@&&tnsna me

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_attrib utes (
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_dat e 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', 'ApprovalPendin g',
'Rejected','Dis continued') ),
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_ite m 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','Shi pped')),
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_se q 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;

[newcomer19992000]

Re: Converting Oracle SQL to MS-SQLServer SQL

You can reffer

for document

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part2/c0761.asp

for a small tool

http://download.com.com/3000-2210-10211217.html?tag=lst-0-1