(I fear what I wish to accomplish shall not be easy, but a lot of my googling around about it yielded rather dated results, so here it goes...)

I have a Postgres 8.4.x database with over 100 tables. I need to recreate these tables in SQL Server 2008. I'm not concerned about the data, just the structure.

Are there any slick tools, shortcuts or suggestions to accomplish this? Heck, I'll even take the find-and-replace values to run on Postgres CREATE TABLE scripts!


  • have you had any luck with this?
    – swasheck
    Apr 5 '12 at 22:56

I've done this with a linked server (though on a smaller scale) so here's what I did:

  1. Set up the linked server using postgresql odbc driver (create odbc dns)
  2. Fetch table information on the linked server using some combination of sp_tables_ex and this SO post
  3. Iterate through those tables using your chosen method of pain: Cursor, Loop, script of some sort and build a query with SELECT * INTO db.schema.@TABLE_NAME FROM OPENQUERY('LINKEDSERVER', 'SELECT * FROM @TABLE_NAME' where @TABLE_NAME is retrieved from the info captured in step 2.

I tried doing this with SSIS but couldn't get the components to play nicely. Again, this is just a rough sketch of what I did. If you'd like more details just ask in a comment and I'd be happy to give them.

EDIT (I just added the code anyway):

USE [master]
EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRES', @srvproduct=N'',
 @provider=N'MSDASQL', @datasrc=N'postgres'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRES',@useself=N'False',


create database workdb

use workdb

sp_configure 'Show Advanced Options', 1
sp_configure 'Ad Hoc Distributed Queries', 1

SELECT * INTO workdb.dbo.pg_tables 
    'Server=(local);Trusted_Connection=yes;','EXEC sp_tables_ex ''POSTGRES''');

select * from pg_tables;

declare @tablename as nvarchar(150)
declare @sql as nvarchar(1000);

declare tablegetter cursor for 
    select table_name from pg_tables

open tablegetter
fetch next from tablegetter
    into @tablename

while @@FETCH_STATUS = 0
        set @sql = 'select * into [' + @tablename + 
            '] from OPENQUERY(POSTGRES, ''SELECT * FROM "' + 
            @tablename + '"'');';
        print @sql;
        fetch next from tablegetter
            into @tablename;

close tablegetter;
deallocate tablegetter;
  • Thanks, @swasheck - this is the solution that I will likely pursue. If you have specific experience with it and have anything to add to this question dba.stackexchange.com/questions/15678/… ... that'd be great! :)
    – JHFB
    Apr 9 '12 at 16:56
  • 1
    No worries. I commented there, referring back to here :). This is just an initial, Q&D setup. I went through and double-checked my data types and had to do some additional modifications for indexes and keys (e.g. eliminating nullable fields)
    – swasheck
    Apr 9 '12 at 17:26

We found a solution for this that ended up being super-fast: using a wizard in our ETL tool Pentaho Data Integration (Kettle). Open a new job then Tools -> Wizard -> Copy Tables. I wouldn't be surprised if other ETL tools had similar wizards.

  • +1 I use pentaho too, it can copy literally anything to anything. In the case of Postgres though I do a lot of spatial data work to so I use a close cousin of Pentaho called 'GeoKettle' which handles spatial stuff too.
    – shawty
    Aug 24 '12 at 14:33

Use pg_dump with the --schema-only option.

That will create a SQL script with all your tables, indexes, views and so on.

Then you'll probably need to do some search & replace especially on the datatypes, but most of the syntax should be fine (except for any stored function and triggers of course)

  • Good solution, though we will probably go the linked table route. Thanks, @a_horse_with_no_name!
    – JHFB
    Apr 9 '12 at 16:57

You can use SQLWays tool for Postgre to SQL Server migration. It converts DDL, schema, data, stored procedures, triggers, embedded SQL and database API with an automation rate from 75 to 95 percent (up to 100 while appropriately tuned).

I'm representing Ispirer Systems, the vendor company. For more info contact me at dmitry.samsonov@ispirer.com


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.