Postgresql Schema To Sql Server
I have found a faster and easier way to accomplish this.First copy your table (or query) to a tab delimited file like so: COPY (SELECT siteid, searchdist, listtype, list, sitename, county, street,city, state, zip, georesult, elevation, lat, lng, wkt, unlocatedbool,id, status, standardstatus, dateopenedorreported, dateclosed,notes, listtypedescription FROM mlocal) TO 'c:SQLAzureImportFilesdatascriptmlocal.tsv' NULL E'Next you need to create your table in SQL, this will not handle any schema for you. The schema must match your exported tsv file in field order and data types.Finally you run SQL's bcp utility to bring in the tsv file like so: bcp MyDb.dbo.mlocal in 'NEWDBSERVERSQLAzureImportFilesdatascriptmlocal.tsv' -S tcp:YourDBServer.database.windows.net -U YourUserName -P YourPassword -cA couple of things of note that I encountered. Postgres and SQL Server handle boolean fields differently.
Sql Server To Postgresql
Your SQL Server schema need to have your boolean fields set to varchar(1) and the resulting data will be 'f', 't' or null. You will then have to convert this field to a bit. Doing something like: ALTER TABLE mlocal ADD unlocated bit;UPDATE mlocal SET unlocated=1 WHERE unlocatedbool='t';UPDATE mlocal SET unlocated=0 WHERE unlocatedbool='f';ALTER TABLE mlocal DROP COLUMN unlocatedbool;Another thing is the geography/geometry fields are very different between the two platforms. Export the geometry fields as WKT using STAsText(geo) and convert appropriately on the SQL Server end.There may be more incompatibilities needing tweaks like this.EDIT. So whereas this technique does technically work, I am trying to transfer several million records from 100+ tables to SQL Azure and bcp to SQL Azure is pretty flaky it turns out.
Postgresql Schema To Sql Server Function
I keep getting intermittent Unable to open BCP host data-file errors, the server is intermittently timing out and for some reason some records are not getting transferred with no indications of errors or problems. So this technique is not stable for transferring large amounts of data to Azure SQL.