Needed to convert a client Microsoft SQLSERVER database to our PostgreSQL database server.
Lots of tables, severall Gigabytes of data.

In Windows:

• open the SQL Server administration console for the databae and do a DIR > tables.txt to get a file with all the table names;
• remove all the tables you will not be needing from the file;
• now to export all the tables data to their files with a one-liner:

  FOR /F %A IN ('type tables.txt') DO bcp "dbo.%A" out "d:\export\path\%A.csv" -S SERVERNAME\SQLSERVEREXPRESS -d "databasename" -U SERVERNAME\username -C RAW -T -c -t "\t" -r "\n"

• Notice the column separator set to TAB \t, and row separator to newline \n;
• Codepage set to RAW (this is an ISO8859-1);
• This is done on a local machine so using a trusted connection;
• This line is executed in the Windows console (terminal);
• Mind that the tables.txt needs to have only the table names per line. I removed the “dbo.” prefix in the file, so had to include it in the parameter in “dbo.%A” parameter, because I want the CSV files not to have the dbo. prefix;
• So in short, the tables.txt file is iterated, each line containing a table name being passed in the %A variables, so that bcp command creates the CSV file.

In Linux:

• copy the previously exported data files folder to the linux machine;
• verify the file encoding $ file -bi filename.csv
• convert all the folder CSV files from ISO8859-1 charset to UTF-8 if needed: $ recode ISO8859-1..UTF-8 *.csv
• now because I need to have the data validated and reassociated, I have Ruby scripts to perform the import, using Datamapper ORM. One can use the CSV class to proceed and then do whatever needs with the data. In this example I get the data into an Hash:

fx_tip = "./../exported_data/some_table.csv"
h_tip = Hash.new
CSV.foreach(fx_tip, {:col_sep => "\t", :headers => false}) do |f|
  h_tip.merge!(f[0] => f[1])
end
puts h_tip.to_yaml


You can even specify a manual Query to export the SQL Server data, using:
FOR /F %A IN ('type tables.txt') DO sqlcmd -E -S SERVERNAME\SQLSERVEREXPRESS -d database -o %A -s ";" -W -Q "SELECT TOP 100 * FROM dbo.%A WHERE someting"

If your case is simpler and you need to make a straight port, you can export the SQL statements from SQL Server and then on Postgres something like:
COPY tablename FROM '/tmp/table.data' WITH DELIMITER ',';

Used Tools:
Microsoft SQL SERVER
Microsoft Windows 7
PostgreSQL
Ubuntu Linux
Ruby Language

posted by pedro mg at 1:31 am & filed under linux, shell, windows, unix, postgres, postgresql, database |