I've done this with a linked server (though on a smaller scale) so here's what I did:
- Set up the linked server using postgresql odbc driver (create odbc dns)
- Fetch table information on the linked server using some combination of
sp_tables_ex
and this SO post
- 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;