Convert MS SQL Server Tables to Oracle
RazorSQL includes a database conversion tool that can convert one or many MS SQL Server tables to Oracle.
The conversion tool can be
accessed via the following menu option in RazorSQL:
DB Tools -> Database Conversion
Inside the Database Conversion menu there are options for converting one table or
to convert a selection of tables within
a specific schema or database.
Selecting the Single Table option brings up the following screen:
Information needed on the screen includes the existing MS SQL Server table to convert, the name of the new Oracle table to create, and whether to execute the conversion directly on a Oracle database connection or whether to generate an SQL script file with the DDL and SQL insert statements for the conversion.
After clicking the convert button, RazorSQL will generate a OraclegreSQL specific create table statement for the MS SQL Server table. It will also generate an SQL insert statement for each row in the MS SQL Server table that can be executed against the new Oracle table. Listed below is an example SQL script that would be generated by the RazorSQL MS SQL Server to Oracle conversion tool.
CREATE TABLE employee (
ssn VARCHAR2(25) NOT NULL,
fname VARCHAR2(25),
minit CHAR(1),
lname VARCHAR2(50),
address VARCHAR2(50),
sex CHAR(1),
salary NUMBER(10),
superssn VARCHAR2(50),
dno NUMBER(10),
PRIMARY KEY (ssn)
);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('123456789', 'John5', 'B', 'Smith', '731 Fondren, Houston TX', 'M', 30000, '333445555', 5);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('333445555', 'Franklin', 'T', 'Wong', '638 Voss, Houston TX', 'M', 40000, '888665555', 5);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('453453453', 'Joyce', 'A', 'English', '5631 Rice, Houston TX', 'F', 25000, '333445555', 5);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('666884444', 'Ramesh', 'K', 'Narayan', '975 Fire Oak, Humble TX', 'M', 38000, '333445555', 5);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('987654321', 'Jennifer', 'S', 'Wallace', '291 Berry, Bellaire, TX', 'F', 43000, '888666555', 4);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('987987987', 'Ahmad', 'V', 'Jabbar', '980 Dallas, Houston TX', 'M', 25000, '987654321', 1);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('999887777', 'Alicia', 'J', 'Zelaya', '3321 Castle, Spring TX', 'F', 25000, '987654321', 4);
/*
ALTER TABLE employee
ADD FOREIGN KEY (dno)
REFERENCES department (dnumber);
*/
When electing to convert multiple tables, in addition to the create table statement and SQL insert statements, RazorSQL can generate alter table statements to set up foreign key relationships. Listed below is an example MS SQL Server to Oracle conversion script that includes the alter table statement to create the foreign key on the Project table that references the Department table.
CREATE TABLE department (
dnumber NUMBER(10) NOT NULL,
dname VARCHAR2(25) NOT NULL,
PRIMARY KEY (dnumber)
);
CREATE TABLE employee (
ssn VARCHAR2(25) NOT NULL,
fname VARCHAR2(25),
minit CHAR(1),
lname VARCHAR2(50),
address VARCHAR2(50),
sex CHAR(1),
salary NUMBER(10),
superssn VARCHAR2(50),
dno NUMBER(10),
PRIMARY KEY (ssn)
);
INSERT INTO department(dnumber, dname) VALUES (1, 'Headquarters');
INSERT INTO department(dnumber, dname) VALUES (4, 'Administration');
INSERT INTO department(dnumber, dname) VALUES (5, 'Research');
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('123456789', 'John5', 'B', 'Smith', '731 Fondren, Houston TX', 'M', 30000, '333445555', 5);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('333445555', 'Franklin', 'T', 'Wong', '638 Voss, Houston TX', 'M', 40000, '888665555', 5);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('453453453', 'Joyce', 'A', 'English', '5631 Rice, Houston TX', 'F', 25000, '333445555', 5);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('666884444', 'Ramesh', 'K', 'Narayan', '975 Fire Oak, Humble TX', 'M', 38000, '333445555', 5);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('987654321', 'Jennifer', 'S', 'Wallace', '291 Berry, Bellaire, TX', 'F', 43000, '888666555', 4);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('987987987', 'Ahmad', 'V', 'Jabbar', '980 Dallas, Houston TX', 'M', 25000, '987654321', 1);
INSERT INTO employee(ssn, fname, minit, lname, address, sex, salary, superssn, dno) VALUES ('999887777', 'Alicia', 'J', 'Zelaya', '3321 Castle, Spring TX', 'F', 25000, '987654321', 4);
ALTER TABLE employee
ADD FOREIGN KEY (dno)
REFERENCES department (dnumber);
