Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
have you had any luck with this? –  swasheck Apr 5 '12 at 22:56

4 Answers 4

up vote 1 down vote accepted

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;
share|improve this answer
    
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.

share|improve this answer
    
+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)

share|improve this answer
    
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

share|improve this answer
    
Thanks for clearly disclosing your involvement in SQLWays - hope you don't mind the minor edit –  Jack Douglas Mar 30 '12 at 10:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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