![\"DB](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/DB-Migration-Herausforderung-480x480.webp\")
<\/a><\/h2>\nWhy use SQL Workbench\/J?<\/h2>\n
The free tool support provided by SQL Workbench\/J can provide a solution to such challenges. As part of our InnovateSAP<\/strong> initiative, we rely on SQL Workbench\/J to solve migration challenges.<\/p>\nThe required connection to source and target databases as well as the maintenance of predefined and additional JDBC drivers (e.g. for HANA) is relatively uncomplicated. A HANA JDBC driver has been created here:<\/p>\n
![\"Manage](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/Treiber-verwalten-640x403.webp\")
<\/a><\/p>\nHere is a PostgreSQL connection<\/a> (which uses an SSH tunnel to SAP BTP):<\/p>\n![\"Select](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/Verbindungsprofil-waehlen-628x480.webp\")
<\/a><\/p>\nSQL Workbench\/J supports a wide range of databases, including (sometimes more, sometimes less officially): Oracle, MySQL, PostgreSQL, SQL Server, DB2, Sybase, MS Access, Firebird, Apache Derby, HSQLDB, Informix, InterSystems Cach\u00e9, MariaDB, Netezza, Pervasive PSQL, SQLite, Teradata, Amazon Redshift, Vertica, Greenplum, SAP MaxDB, Snowflake, Amazon Athena, Apache Hive, Google BigQuery, Presto, SAP HANA. This broad support makes it possible to handle virtually any data migration project in the SQL area.<\/p>\n
Common migration hurdles<\/h2>\n
For example, one of the common challenges is blob fields (binary data of almost unlimited size) that cannot be exported as CSV, Excel or JSON with Oracle’s Oracle SQL Developer, for example. These files could become extremely large due to binary data.<\/p>\n
In these cases, a manual export and import of files, which is already very labor-intensive, is not only error-prone, time-consuming and difficult to reproduce – it is simply impossible.<\/p>\n
Creating Data Structures<\/h2>\n
Depending on the application, the creation of tables, indexes and, if necessary, functions can be done either via SQL directly or through your development tools. Artificial intelligence can be of particular help here, for example when creating the new data model based on the old data structure or adapting the Create SQL scripts to a new manufacturer or namespace. ChatGPT 3.5 is already a good aid here.<\/p>\n
Implementation<\/h2>\n
With SQL Workbench\/ J’s Data Pumper<\/strong> , you can perform data migration efficiently. Once the connections to the source and target databases have been established, the migration can be controlled with a comprehensive set of options:<\/p>\n\n- Selecting the Data Source (Table or SQL Select) and Destination – Here using SQL Source instead of a table:<\/li>\n<\/ul>\n
![\"\"](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/Uebertragung-von-SQL-Query-640x400.webp\")
<\/a><\/p>\n\n- Manual assignment of table columns, if necessary<\/li>\n
- Specifying the Mode to Control Update\/Replace Existing Values in the Target<\/li>\n
- Setting Batch Size for Optimized Performance<\/li>\n<\/ul>\n
A key aspect of this process is the choice of mode to determine how or if existing values should be replaced. An incorrect selection will lead to errors at runtime. Possible modes are:<\/p>\n
\n- Insert<\/li>\n
- Update<\/li>\n
- Insert+Update<\/li>\n
- Update+Insert<\/li>\n
- Upsert<\/li>\n<\/ul>\n
For performance reasons and to avoid errors, it is advisable to choose the mode that allows successful operations more frequently, i.e. insert on non-existent keys or upsert to update existing values or add new records. To ensure optimal performance, it is recommended to insert or update records in batches, typically in sizes of 100 records per batch, to avoid excessive commits.<\/p>\n
![\"Transfer](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/Uebertragung-mit-Where-1920x1200.webp\")
<\/a>
\nWith the support of SQL Workbench\/J, data can be migrated effectively and quickly.<\/p>\nExportable script<\/h2>\n
To perform a data migration as scheduled, SQL Workbench\/J allows you to export the Data Pumper function as a script. In this way, you can create an executable script step by step with one WB Copy command per table. The migration is then carried out at the push of a button. If necessary, normal SQL commands can also be used in the script.<\/p>\n
Here is an example where a table is migrated from Oracle to SAP ASE (formerly Sybase SQL Server) using a SQL query and default values are set.<\/p>\n
WbCopy -sourceProfile='p75 sys'\n -sourceGroup=intranet\n -targetProfile='p1x sysdba ase'\n -targetGroup=intranet\n -targetTable=SAPSR3DB.TCM_EMPLOYEE_DATE\n -sourceQuery=\"SELECT ID,\n CONTACT_INTERN,\n IS_BACKOFFICE,\n NVL(HIRE_DATE, TO_DATE('2099-01-01','YYYY-MM-DD')) as HIRE_DATE,\n NVL(FIRE_DATE, TO_DATE('2099-01-01','YYYY-MM-DD')) as FIRE_DATE,\n VACATION_DAYS\nFROM SAPSR3DB.TCM_EMPLOYEE_DATE\n\n\"\n -ignoreIdentityColumns=false\n -deleteTarget=true\n -continueOnError=false\n -batchSize=100\n;<\/pre>\nSuch a script makes data migration between different database providers simple and straightforward.<\/p>\n
![\"DB](\"https:\/\/www.ho2-gmbh.de\/wp-content\/uploads\/2024\/03\/DB-Migration-Erfolgreich-Dirigiert-480x480.webp\")
<\/a><\/h2>\nContact<\/h2>\n
We will be happy to answer any further questions you may have about data migration and the InnovateSAP<\/strong> initiative. If you have any challenges or specific requests, do not hesitate to contact us. Together we will find a solution!<\/p>\n","protected":false},"excerpt":{"rendered":"Discover how to successfully migrate data between disparate databases with SQL Workbench\/J and InnovateSAP without the usual pitfalls and complications.<\/p>\n","protected":false},"author":1,"featured_media":6347,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"wpupg_custom_link":[],"wpupg_custom_link_behaviour":[],"wpupg_custom_link_nofollow":[],"wpupg_custom_image":[],"wpupg_custom_image_id":[],"footnotes":""},"categories":[677,511,497],"tags":[752,746,573,747,749,748,744,755,743,727,745,751,721,510,750,726,753],"class_list":{"0":"post-6366","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-general-en","8":"category-sap-btp-en","9":"category-sap-code-en","10":"tag-batch-insert-en","11":"tag-blob-fields","12":"tag-btp-en","13":"tag-cap-application","14":"tag-data-pumper-en","15":"tag-database-migration","16":"tag-hana-database","17":"tag-howto-en","18":"tag-jdbc-driver","19":"tag-migration","20":"tag-oracle-sql-developer-en","21":"tag-performance-en","22":"tag-postgresql-en","23":"tag-sap-btp-en","24":"tag-script-export","25":"tag-sql-workbench-j-en","26":"tag-tool-support"},"yoast_head":"\n
How to successfully migrate data between different databases - HO2 Systemberatung GmbH<\/title>\n<meta name=\"description\" content=\"Your Guide to Seamless Data Migration Between Disparate DB Providers with SQL Workbench\/J, Presented by InnovateSAP\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.ho2-gmbh.de\/en\/news\/how-to-successfully-migrate-data-between-different-dbs\/\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to successfully migrate data between different databases - HO2 Systemberatung GmbH","description":"Your Guide to Seamless Data Migration Between Disparate DB Providers with SQL Workbench\/J, Presented by InnovateSAP","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.ho2-gmbh.de\/en\/news\/how-to-successfully-migrate-data-between-different-dbs\/","schema":{"@context":"https:\/\/schema.org","@graph":[]}},"_links":{"self":[{"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/posts\/6366","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/comments?post=6366"}],"version-history":[{"count":2,"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/posts\/6366\/revisions"}],"predecessor-version":[{"id":6387,"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/posts\/6366\/revisions\/6387"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/media\/6347"}],"wp:attachment":[{"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/media?parent=6366"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/categories?post=6366"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ho2-gmbh.de\/en\/wp-json\/wp\/v2\/tags?post=6366"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
The required connection to source and target databases as well as the maintenance of predefined and additional JDBC drivers (e.g. for HANA) is relatively uncomplicated. A HANA JDBC driver has been created here:<\/p>\n
Here is a PostgreSQL connection<\/a> (which uses an SSH tunnel to SAP BTP):<\/p>\n SQL Workbench\/J supports a wide range of databases, including (sometimes more, sometimes less officially): Oracle, MySQL, PostgreSQL, SQL Server, DB2, Sybase, MS Access, Firebird, Apache Derby, HSQLDB, Informix, InterSystems Cach\u00e9, MariaDB, Netezza, Pervasive PSQL, SQLite, Teradata, Amazon Redshift, Vertica, Greenplum, SAP MaxDB, Snowflake, Amazon Athena, Apache Hive, Google BigQuery, Presto, SAP HANA. This broad support makes it possible to handle virtually any data migration project in the SQL area.<\/p>\n For example, one of the common challenges is blob fields (binary data of almost unlimited size) that cannot be exported as CSV, Excel or JSON with Oracle’s Oracle SQL Developer, for example. These files could become extremely large due to binary data.<\/p>\n In these cases, a manual export and import of files, which is already very labor-intensive, is not only error-prone, time-consuming and difficult to reproduce – it is simply impossible.<\/p>\n Depending on the application, the creation of tables, indexes and, if necessary, functions can be done either via SQL directly or through your development tools. Artificial intelligence can be of particular help here, for example when creating the new data model based on the old data structure or adapting the Create SQL scripts to a new manufacturer or namespace. ChatGPT 3.5 is already a good aid here.<\/p>\n With SQL Workbench\/ J’s Data Pumper<\/strong> , you can perform data migration efficiently. Once the connections to the source and target databases have been established, the migration can be controlled with a comprehensive set of options:<\/p>\n A key aspect of this process is the choice of mode to determine how or if existing values should be replaced. An incorrect selection will lead to errors at runtime. Possible modes are:<\/p>\n For performance reasons and to avoid errors, it is advisable to choose the mode that allows successful operations more frequently, i.e. insert on non-existent keys or upsert to update existing values or add new records. To ensure optimal performance, it is recommended to insert or update records in batches, typically in sizes of 100 records per batch, to avoid excessive commits.<\/p>\n To perform a data migration as scheduled, SQL Workbench\/J allows you to export the Data Pumper function as a script. In this way, you can create an executable script step by step with one WB Copy command per table. The migration is then carried out at the push of a button. If necessary, normal SQL commands can also be used in the script.<\/p>\n Here is an example where a table is migrated from Oracle to SAP ASE (formerly Sybase SQL Server) using a SQL query and default values are set.<\/p>\n Such a script makes data migration between different database providers simple and straightforward.<\/p>\n We will be happy to answer any further questions you may have about data migration and the InnovateSAP<\/strong> initiative. If you have any challenges or specific requests, do not hesitate to contact us. Together we will find a solution!<\/p>\n","protected":false},"excerpt":{"rendered":" Discover how to successfully migrate data between disparate databases with SQL Workbench\/J and InnovateSAP without the usual pitfalls and complications.<\/p>\n","protected":false},"author":1,"featured_media":6347,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"wpupg_custom_link":[],"wpupg_custom_link_behaviour":[],"wpupg_custom_link_nofollow":[],"wpupg_custom_image":[],"wpupg_custom_image_id":[],"footnotes":""},"categories":[677,511,497],"tags":[752,746,573,747,749,748,744,755,743,727,745,751,721,510,750,726,753],"class_list":{"0":"post-6366","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-general-en","8":"category-sap-btp-en","9":"category-sap-code-en","10":"tag-batch-insert-en","11":"tag-blob-fields","12":"tag-btp-en","13":"tag-cap-application","14":"tag-data-pumper-en","15":"tag-database-migration","16":"tag-hana-database","17":"tag-howto-en","18":"tag-jdbc-driver","19":"tag-migration","20":"tag-oracle-sql-developer-en","21":"tag-performance-en","22":"tag-postgresql-en","23":"tag-sap-btp-en","24":"tag-script-export","25":"tag-sql-workbench-j-en","26":"tag-tool-support"},"yoast_head":"\n<\/a><\/p>\n<\/a><\/p>\nCommon migration hurdles<\/h2>\n
Creating Data Structures<\/h2>\n
Implementation<\/h2>\n
\n
<\/a><\/p>\n\n
\n
<\/a>
\nWith the support of SQL Workbench\/J, data can be migrated effectively and quickly.<\/p>\nExportable script<\/h2>\n
WbCopy -sourceProfile='p75 sys'\n -sourceGroup=intranet\n -targetProfile='p1x sysdba ase'\n -targetGroup=intranet\n -targetTable=SAPSR3DB.TCM_EMPLOYEE_DATE\n -sourceQuery=\"SELECT ID,\n CONTACT_INTERN,\n IS_BACKOFFICE,\n NVL(HIRE_DATE, TO_DATE('2099-01-01','YYYY-MM-DD')) as HIRE_DATE,\n NVL(FIRE_DATE, TO_DATE('2099-01-01','YYYY-MM-DD')) as FIRE_DATE,\n VACATION_DAYS\nFROM SAPSR3DB.TCM_EMPLOYEE_DATE\n\n\"\n -ignoreIdentityColumns=false\n -deleteTarget=true\n -continueOnError=false\n -batchSize=100\n;<\/pre>\n<\/a><\/h2>\nContact<\/h2>\n