I've got a PostgreSQL database that I wish to proceed to SQL Server -- both schema and data. I'm poor so I'd rather not pay anything. I'm also lazy, so I'd rather not do greatly work. Presently I am carrying this out table by table, and you will find about 100 tables to complete. This really is very tiresome.
Can there be some kind of trick that does things i want?
In my opinion you will probably have become lower-votes because of amazing easy producing an easy SQL script from PostgreSQL that may (theoretically) be run again nearly any DBMS. If your are a normal PostgreSQL user, it sounds just like a dumb question.
That isn't fair because it works out this really is really a moderately hard problem (although more because of SQL Server's odd syntax and interface than any failing of PostgreSQL).
You need to have the ability to find some helpful information within the recognized answer within this Serverfault page: http://serverfault.com/questions/65407/best-tool-to-migrate-a-postgresql-database-to-ms-sql-2005.
If you're able to obtain the schema converted with no data, you might have the ability to shorten the steps for that data applying this command:
pg_dump --data-only --column-inserts your_db_name > data_load_script.sql
This load is going to be quite slow, however the --column-card inserts option creates probably the most generic Place claims feasible for each row of information and really should be compatible.
EDIT: Suggestions on transforming the schema follows:
I'd begin by dumping the schema, but getting rid of something that has related to possession or permissions. This ought to be enough:
pg_dump --schema-only --no-owner --no-privileges your_db_name > schema_create_script.sql
Edit this file to include the road "BEGIN TRANSACTION" towards the beginning and "ROLLBACK TRANSACTION" towards the finish. You can now load it and run it inside a query window in SQL Server. When you get any errors, make certain put forth the foot of the file, highlight the ROLLBACK statement and run it (by striking F5 as the statement is outlined).
Essentially, you need to resolve each error before the script runs through cleanly. You'll be able to alter the "ROLLBACK TRANSACTION" to "COMMIT TRANSACTION" and run the last time.
Regrettably, I am unable to assist with which errors you might see as I have not gone from PostgreSQL to SQL Server, only the other way round. Things that I'd be prepared to be an problem, however (clearly, No thorough list):
- PostgreSQL does auto-increment fields by connecting a NOT NULL INTEGER area to some SEQUENCE utilizing a DEFAULT. In SQL Server, it is really an IDENTITY column, but they are not the identical factor. I am unsure if they're equivalent, but when your original schema is filled with "id" fields, you might be set for some trouble. I'm not sure if SQL Server has CREATE SEQUENCE, so you might want to remove individuals.
- Database functions / Saved Methods don't translate between RDBMS platforms. You will need to remove any CREATE FUNCTION claims and translate the calculations by hand.
- Be cautious about encoding from the computer file. I am a Linux person, and so i have no clue how you can verify encoding in Home windows, but you have to make certain that what SQL Server needs is equivalent to the file you're posting from PostgreSQL. _pg_dump_ comes with an option --encoding= that enables you to set a particular encoding. I appear to recall that Home windows has a tendency to use two-byte, UTF-16 encoding for Unicode where PostgreSQL uses UTF-8. I'd some problem going from SQL Server to PostgreSQL because of UTF-16 output so it might be worth researching.
- The PostgreSQL datatype TEXT is only a VARCHAR with no max length. In SQL Server, TEXT is... complicated (and deprecated). Each area inside your original schema which are declared as TEXT will have to be examined to have an appropriate SQL Server data type.
- SQL Server has extra data types for UNICODE data. I am different enough by using it to create suggestions. I am just mentioning it might be an problem.
pg_dump dbname > outfile
where 1. dbname = database you need to export. 2. outfile =title from the file which is produced. It'll create a *.sql apply for you. Carry it out in MS-SQL