Convert MS SQL Server Tables to PostgreSQL
RazorSQL includes a database conversion tool that can convert one or many MS SQL Server tables to PostgreSQL.
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 PostgreSQL table to create, and whether to execute the conversion directly on a PostgreSQL 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 PostgreSQL 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 PostgreSQL table. Listed below is an example SQL script that would be generated by the RazorSQL MS SQL Server to PostgreSQL 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),
salary int,
superssn varchar(50),
dno int,
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 PostgreSQL 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 numeric NOT NULL,
DNAME varchar(25) NOT NULL,
PRIMARY KEY (DNUMBER)
);
CREATE TABLE PROJECT (
PNUMBER numeric NOT NULL,
PNAME varchar(50),
PLOCATION varchar(50),
DNUM numeric,
PRIMARY KEY (PNUMBER)
);
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 DEPARTMENT(DNUMBER, DNAME) VALUES (7, 'test2');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (8, 'test3');
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (1, 'ProductX', 'Bellaire', 5);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (2, 'ProductY', 'Sugarland', 5);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (3, 'ProductZ', 'Houston', 5);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (10, 'Computerization', 'Stafford', 4);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (20, 'Reorganization', 'Houston', 1);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (30, 'Newbenefits', 'Stafford', 4);
ALTER TABLE PROJECT
ADD FOREIGN KEY (DNUM)
REFERENCES DEPARTMENT (DNUMBER);
