ASP.Net Core 2 with Postgresql on Linux Part 2: Move SQL Server Data to Postgres

In Part I, I discussed  the goal of porting my ASP.Net Core 2 app to my Linux system using  Postgresql as the database and placing it on Heroku. As I thought about it, my first logical step was to port the data from SQL Server to Postgresql.

While I think I am proficient at the command prompt, I had to admit that having a GUI based tool would be a help. Therefore, Part I details the setup of pgAdmin installed on my Ubuntu system.

Here, in Part II we port the existing SQL Server data to Postgres.

First, I looked around for a free tool to take an existing MS SQL database and transfer it to a Postgresql database. However, what I found did not seem to meet my goal. Given the disparity between the Db platforms, the best tool I could find was a Postgresql function entitled convert_mssqlddl2pgsql.sql. Since I had existing MS SQL Create Table scripts already, this seemed the best option.

In short, I loaded the convert_mssqlddl2pgsql script into pgAdmin and added my MS SQL Create Table script (for each existing table, one at a time) as the input parameter (see below), and the output was a Postgres Create Table script.

Here is the SQL for the SQL Server table

 CREATE TABLE [dbo].[myTable](  
   [myID] [int] IDENTITY(1,1) NOT NULL,  
   [FirstValue] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,  
   [SecondValue] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,  
   [ThirdValue] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,  
  CONSTRAINT [PK_MyID] PRIMARY KEY CLUSTERED   
 (  
   [ContactID] ASC  
 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
 ) ON [PRIMARY]  

re is the Data Output from pgAdmin function above:

 create table public.mytable(  
   myid serial not null,  
   firstvalue varchar(50) null,  
   secondvalue varchar(50) null,  
   thirdvalue varchar(50) null,  
  constraint pk_myid primary key   
 (  
   myid  
 )   
 )                  

After running the pgAdmin function for each SQL Server database table and then running the script in pgAdmin to create the Postgres database tables, I made sure that the sequence columns were set accordingly. To do this I ran the following for each table with a serial id column:

 UPDATE public.mytable SET myid=nextval('mytable_myid_seq');  

You can use pgAdmin to check the serial id columns to may sure that the default value property of the column is set to the next available value.

The next step was to get the data from the MS SQL database tables into the newly formed Postgresql tables. What I did was to get a record-by-record text file output of the SQL Server table from a select statement that consisted of the table values.

 'Your first column value here', 'Your second column value here...','and you guest it....your third column here!'  
 'Your first column value here again', 'Your second column value here..yeah...','and your third column here!'  
 'Your first column value here, one more time', 'Your second value','your third column!'  

Now that I had the SQL Server table data into a fixed length format, the next step was to transform it into proper Postgres insert statements.

Mind you, I know that I could have used a SQL select statement within SQL Server to create the Postgres insert statements, but given that I have been a Ruby fan from way back and reading in and parsing files is one of the strengths of Ruby, the next step was a natural evolution, or at least some kind of semi-intelligent design...I used the Ruby script below to produce the Postgres insert scripts.

 file = File.open("myValues.txt", 'r')  
 while !file.eof?  
   line = file.readline  
   puts "INSERT INTO public.tableName(firstColumn, secondColumn, thirdYouGuestItColumn) VALUES ( " + line + ");"  
 end  

The output was the following:

 INSERT INTO public.myTable(  
      firstvalue, secondvalue, thirdvalue)  
      VALUES ('Your first column value here', 'Your second column value here...', 'and you guest it....your third column here!');  
 INSERT INTO public.myTable(  
      firstvalue, secondvalue, thirdvalue)  
      VALUES ('Your first column value here again', 'Your second column value here..yeah...', 'and your third column here!');  
 INSERT INTO public.myTable(  
      firstvalue, secondvalue, thirdvalue)  
      VALUES ('Your first column value here, one more time', 'Your second value', 'your third column!');  

Just as before, I ran the above script in the pgAdmin client. The result was Postgresql tables with data!

In short, these are the steps that took place with each table.

Now that my data is in Postgresql, the next step, to be detailed in Part III, I create the ASP.Net MVC Core 2.x app, from which we will proceed, and get Entity Framework installed.