Easiest way to migrate a PostgreSQL database into an SQL Server one -


i have postgresql database want move sql server -- both schema , data. poor don't want pay money. lazy, don't want work. i'm doing table table, , there 100 tables do. extremely tedious.

is there sort of trick want?

i believe may have gotten down-votes due amazing ease of generating simple sql script postgresql can (theoretically) run again dbms. if 1 regular postgresql user, sounds dumb question.

that's not fair since turns out moderately hard problem (although more due sql server's odd syntax , interface failing of postgresql).

you should able find useful information in accepted answer in serverfault page: https://serverfault.com/questions/65407/best-tool-to-migrate-a-postgresql-database-to-ms-sql-2005.

if can schema converted without data, may able shorten steps data using command:

pg_dump --data-only --column-inserts your_db_name > data_load_script.sql

this load quite slow, --column-inserts option generates generic insert statements possible each row of data , should compatible.

edit: suggestions on converting schema follows:

i start dumping schema, removing has ownership or permissions. should enough:

pg_dump --schema-only --no-owner --no-privileges your_db_name > schema_create_script.sql

edit file add line begin transaction; beginning , rollback transaction; end. can load , run in query window in sql server. if errors, make sure go bottom of file, highlight rollback statement , run (by hitting f5 while statement highlighted).

basically, have resolve each error until script runs through cleanly. can change rollback transaction commit transaction , run 1 final time.

unfortunately, cannot errors may see have never gone postgresql sql server, other way around. things expect issue, (obviously, not exhaustive list):

  • postgresql auto-increment fields linking not null integer field sequence using default. in sql server, identity column, they're not same thing. i'm not sure if equivalent, if original schema full of "id" fields, may in trouble. don't know if sql server has create sequence, may have remove those.
  • database functions / stored procedures not translate between rdbms platforms. you'll need remove create function statements , translate algorithms manually.
  • be careful encoding of data file. i'm linux person, have no idea how verify encoding in windows, need make sure sql server expects same file importing postgresql. pg_dump has option --encoding= let set specific encoding. seem recall windows tends use two-byte, utf-16 encoding unicode postgresql uses utf-8. had issue going sql server postgresql due utf-16 output worth researching.
  • the postgresql datatype text varchar without max length. in sql server, text is... complicated (and deprecated). each field in original schema declared text need reviewed appropriate sql server data type.
  • sql server has data types unicode data. i'm not familiar enough make suggestions. i'm pointing out may issue.

Comments

Post a Comment

Popular posts from this blog

c++ - Is it possible to compile a VST on linux? -

for class project i'm attempting write vst plugin backed cuda. current cuda workflow on linux box, i'd prefer compile , link there. according wikipedia , should possible (i couldn't find steinberg documentation relevant linux) can't find makefile or instructions on how build if aren't using xcode or visual studio. i'm vst 3 sdk doesn't support linux. when try compile plugin under linux, error: ./base/source/fatomic.cpp:39:30: fatal error: libkern/osatomic.h: no such file or directory this issue caused following code in "vst3 sdk/base/source/fatomic.cpp" #if mac #include <libkern/osatomic.h> #if mac_os_x_version_min_required > mac_os_x_version_10_4 #define native_atomic_type (volatile int32_t*) #else #define native_atomic_type (int32_t*) #endif #elif windows #include <windows.h> #endif but hope compiling under linux work vst sdk 2.4. reading. jvstwrapper seems run on lin...
Read more

java - Output of Eclipse is rubbish -

i want write program in eclipse language different english. when display output using system.out program, displays output correctly in console editor. when accept input console , output it, output rubbish. how can solve it. many thnaks i'm not sure if problem, have character encoding problem. make sure you're using unicode, utf-8. you can read more @ official docs converting non-unicode text .
Read more

jquery - Confused with JSON data and normal data in Django ajax request -

i read json internet still have not got grasp of it. reading article http://webcloud.se/log/ajax-in-django-with-jquery/ i not understood first part function using json def xhr_test(request, format): if request.is_ajax(): if format == 'xml': mimetype = 'application/xml' if format == 'json': mimetype = 'application/javascript' data = serializers.serialize(format, examplemodel.objects.all()) return httpresponse(data,mimetype) # if want prevent non xhr calls else: return httpresponse(status=400) my main problems are from function getting format variable does format json mean data given function json or data recived json can give me simple example ouput of function data = serializers.serialize(format, examplemodel.objects.all()) how use data when response in jquery function if don't use json in above function how input , response chnage thanks ...
Read more