A python method of doing this (for us a daily scripted process) involves reading the PostGIS table using psycopg2 and casting the spatial data as text.
I then use an insert and dump the data into MSSQL using pypyodbc, while using a 'temp_geom' table to hold the 'geometry text', then run a quick function to turn that text back into geometry, then remove the temp_geom column.
Of course, the PostGIS table exists, and the MSSQL table needs to be created in the same schema you're going to be loading into (using a create table (columns) statement)
So the python script looks like this:
import psycopg2
import pypyodbc
#connection to POSTGIS Dev
connSource = psycopg2.connect("host=hostname dbname=pgisdev user=username password=***** ")
curSource = connSource.cursor()
#connection to MSSQL Dev
connDest = pypyodbc.connect("DRIVER={SQL Server};SERVER=hostname;DATABASE=sqldev;UID=user;PWD=****")
curDest = connDest.cursor()
curSource.execute('''
SELECT
elem, mid, high , school_name, abbreviation, schnum
, classification, school_level, current_config, final_config, ST_AsText(geom)
FROM "Schools_Current";
''')
#if repeating, add line to delete * from destination_table
#add temporary text column to hold geometry cast to text
curDest.execute('''
alter table dpsdata.Schools_Current add geom_temp varchar(max);
''')
connDest.commit()
#build first part of insert statement with parameters
sqlDest = '''
INSERT INTO [dpsdata].[Schools_Current]
([ELEM]
,[MID]
,[HIGH]
,[SCHOOL_NAME]
,[ABBREVIATION]
,[SCHNUM]
,[CLASSIFICATION]
,[SCHOOL_LEVEL]
,[CURRENT_CONFIG]
,[FINAL_CONFIG]
,[geom_temp])
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
'''
data = []
rows = curSource.fetchall()
for row in rows:
data = [row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10]]
curDest.execute(sqlDest, data)
connDest.commit()
#re-build geometry into geometry column
curDest.execute('''
update dpsdata.Schools_Current
set shape = geometry::STGeomFromText(geom_temp, 2877)
''')
connDest.commit()
#remove temporary text column
curDest.execute('''
alter table dpsdata.Schools_Current_dev drop column geom_temp;
''')
connDest.commit()
connSource.close()
connDest.close()
It may look like a lot, but we have a few dozen of these deployed and scripted on Windows and Ubuntu servers and they hum along every morning!
Another HUGE benefit is when you're selecting the geom from PostGIS you can use any of the spatial functions to reproject the data, buffer, etc. etc. etc., so the select would look like this:
curSource.execute('''
SELECT
elem, mid, high , school_name, abbreviation, schnum
, classification, school_level, current_config, final_config, ST_AsText(ST_Transform(geom, 2877))
FROM "Schools_Current";
''')
So that means we can store our PostGIS data in WGS84, then load it into MSSQL as State Plane CO Central (2877) for our MSSQL clients...
Best Answer
One other point that may help somebody else... Make sure you're actually in the database which has had the GIS extensions added before you try to grant privileges on it!