PostgreSQL to SQL Server 2008 – Migrating Tables

postgresqlsql-server-2008

(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!

Thanks!

Best Answer

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]
GO
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',
@locallogin=NULL,
@rmtuser=N'username',@rmtpassword='########'

GO

create database workdb
GO

use workdb
GO

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

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

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
    begin
        set @sql = 'select * into [' + @tablename + 
            '] from OPENQUERY(POSTGRES, ''SELECT * FROM "' + 
            @tablename + '"'');';
        print @sql;
        execute(@sql);
        fetch next from tablegetter
            into @tablename;
    end

close tablegetter;
deallocate tablegetter;
Related Question