Convert Oracle Database Tables to MS SQL Server
RazorSQL includes a database conversion tool that can convert one or
many Oracle tables to MS SQL Server tables.
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 Oracle table to convert, the name of the new MS SQL Server table to create, and whether to execute the conversion directly on a MS SQL Server database connection or 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 MS SQL Server specific create table statement for the table. It will also generate an SQL insert statement for each row in the Oracle table that can be executed against the new MS SQL Server table. Listed below is an example SQL script that would be generated by the RazorSQL Oracle to MS SQL Server conversion tool.
CREATE TABLE EMPLOYEE (
SSN VARCHAR(25) NOT NULL,
FNAME VARCHAR(25),
MINIT CHAR(1),
LNAME VARCHAR(50),
ADDRESS VARCHAR(50),
SEX CHAR(1) NOT NULL,
SALARY DECIMAL,
SUPERSSN VARCHAR(50),
DNO DECIMAL,
PRIMARY KEY (SSN)
);
INSERT INTO EMPLOYEE(SSN, FNAME, MINIT, LNAME, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('123456789', 'John', '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 ('999887777', 'Alicia', 'J', 'Zelaya', '3321 Castle, Spring TX', 'F', 25000, '987654321', 4);
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 ('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 ('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 ('987987987', 'Ahmad', 'V', 'Jabbar', '980 Dallas, Houston TX', 'M', 25000, '987654321', 1);
/*
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 Oracle to MS SQL Server conversion script that includes the alter table statement to create the foreign key on the Employee table that references the Department table.
CREATE TABLE DEPARTMENT (
DNUMBER DECIMAL NOT NULL,
DNAME VARCHAR(25) NOT NULL,
PRIMARY KEY (DNUMBER)
);
CREATE TABLE EMPLOYEE (
SSN VARCHAR(25) NOT NULL,
FNAME VARCHAR(25),
MINIT CHAR(1),
LNAME VARCHAR(50),
ADDRESS VARCHAR(50),
SEX CHAR(1) NOT NULL,
SALARY DECIMAL,
SUPERSSN VARCHAR(50),
DNO DECIMAL,
PRIMARY KEY (SSN)
);
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (5, 'Research');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (4, 'Administration');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (1, 'Headquarters');
INSERT INTO EMPLOYEE(SSN, FNAME, MINIT, LNAME, ADDRESS, SEX, SALARY, SUPERSSN, DNO) VALUES ('123456789', 'John', '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 ('999887777', 'Alicia', 'J', 'Zelaya', '3321 Castle, Spring TX', 'F', 25000, '987654321', 4);
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 ('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 ('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 ('987987987', 'Ahmad', 'V', 'Jabbar', '980 Dallas, Houston TX', 'M', 25000, '987654321', 1);
ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DNO)
REFERENCES DEPARTMENT (DNUMBER);
