Linked server (read data from pgsql and insert it into SQL Server)

Question

I have a SQL Server 2005. I have created a linked server to PG SQL server.

It works great.

SELECT * 
FROM OpenQuery(POSTGRESQL_SERV,
               'SELECT comp_id,comp_name FROM company WHERE comp_type = 5')

I need to read all data from PG SQL and insert it into SQL Server.

How to create a while ? (also unique id`s)

Thx.

Answer 1

Not sure if I understand your question entirely (while? unique id's?), but all you'd be doing would be an insert statement:

insert into MyTable ( comp_id, comp_name )
select comp_id, comp_name
from OpenQuery(POSTGRESQL_SERV,'SELECT comp_id,comp_name FROM company WHERE comp_type = 5')

As to creating unique ID's, you'd want to have an IDENTITY column on your destination table, which would create the ID's for you.