In this article, we tell you how to migrate data between databases (a source database and a target database) while making use of DbVisualizer’s import and export features.

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client

Data importing and exporting are important in database development and data analysis because they allow for the integration of data from various originations, making it possible to work with a larger and more diverse set of data. This can be crucial in many fields, such as business, where data from multiple sources is needed to make well-informed decisions.

Data import is the process of bringing in data from an external source into a software system. Data import can involve a variety of exercises, such as converting data into a compatible format, cleaning and transforming data, loading data into a database, etc.

Data export on the other hand, is the process of taking data that is currently stored within a software program or system and conveying it to an external destination.

Setting Up

For the purpose of this article, we’re going to utilize MySQL and PostgreSQL database management systems. To set up both database connections, we will need to first install both DBMS systems and then connect them to DbVisualizer.

Setting Up MySQL

To set up MySQL, navigate to the MySQL download page to download the recommended installer for your operating system. Use these instructions: Windows instruction, macOS instruction, Linux instruction to help you install MySQL on Windows, macOS, and Linux operating systems respectively.

Setting Up Postgres

To install Postgres, navigate to the Postgres download page and download the recommended installer for your operating system. You can follow this tutorial for installation on macOS, this installation tutorial to help you install Postgres on a Windows operating system, and this tutorial for installation on a Linux operating system.

Setting Up DbVisualizer

To install DbVisualizer, navigate to the DbVisualizer download page to download the recommended installer for your operating system.

Run the download installer to install DbVisualizer on your computer. Open DbVisualizer once it has been installed, and you will be prompted to create a database connection.

Connecting to Postgres

Step 1: Run the command below in the terminal of your computer to confirm the successful installation of the Postgres on your computer.

$ psql -U postgres

When the prompt opens, set a password for PostgreSQL from the prompt given.

Now, let us create a database called CPIforecast which will hold records of the average price change over time of consumer goods and services. Run the command below in the terminal of your computer to create the database.

$ CREATE DATABASE cpiforecast;

To check if the database has been created successfully in the list of databases, run the command below.

$ \list

Step 3: Fill in the database connection input fields with the appropriate credentials.

• Name: CPI Forecast
• Database: cpiforecast
• Database Userid: postgres
• Database Password: Your Postgres password

Connecting to MySQL

To create your MySQL database connection, open the MySQL window and follow the steps below.

Step 1: Click on the “Start MySQL Server” button and enter your operating user password to start the server.

Step 2: Open the terminal window of your computer and run the command below to open the MySQL prompt.

$ mysql -u root -p

When the prompt opens, enter the password used during the MySQL installation process.

Step 3: Now, let us create a database called ‘CPIforecast Annex’ by running the command below in the terminal and pressing ‘enter’.

$ CREATE DATABASE cpiforecastannex;

To confirm if the database has been created, run the command below.

$ SHOW DATABASES;

We can see that the “cpiforecastannex” database has been created.

Step 4: Let us now connect the “cpiforecastannex” created to DbVisualizer. Under the “Databases” tab in the DbVisualizer window, click on the “create new database connection button”. Search and select ‘MySQL 8’ from the list of drivers.

Step 5: Fill in the database connection input fields with the appropriate credentials.

• Name: ‘CPI forecast Annex’
• Database: ‘cpiforecastannex’
• Database Userid: ‘root’
• Database Password: password for root user.

Now, you have more than one database connected to DbVisualizer.

Importing Data Table Into Postgres Using DbVisualizer

Before you can migrate data from one database to another, you will typically have to import data into one database before migrating it to another. The process of migration typically involves extracting data from the source database, transforming it if necessary, and then loading it into the target database.

Step 1: Navigate to this CPI Forecast file and download the dataset.

Step 2: Next, Open the database connection CPI Forecast tab tree as shown below.

Change the table name to “CPIForecastData.” Since SQL does not allow spaces in table names, let us rename the column name to something with no spaces by omitting all spaces in the “Changes in Consumer Price Indexes for food, 2020 through 2023” column name.

You should see success in the import log as shown below.

Importing the Data From PostgreSQL Into MySQL

Since both Postgres and MySQL databases are connected, let us navigate to the schema and table we want to export from in the Postgres database. In this case, our table is ‘cpiforecastdata.’

We can see that the table data we imported into our Postgres database has been successfully exported into our MySQL database. Migrating data between databases is an important step in data analysis because it allows for the integration of data from multiple sources. This can be useful in a variety of ways, such as: data warehousing, data integration, data distribution, data replication. The ability to migrate data between databases is critical for organizations that need to make sense of large and complex data sets. It allows them to easily integrate and analyze data from different sources, which in turn can lead to better decision-making, improved efficiency, and increased competitiveness.

Conclusion

In this blog, we have learnt how to migrate data from one database to another while making use of DbVisualizer. This is an important factor in data analysis for organizations that make sense of large and complex data. We hope you enjoyed working with DbVisualizer.

If you're looking for an all-in-one database management tool, look no further than DbVisualizer. With its easy-to-use interface and powerful features, you can easily connect to and manage multiple databases, including MySQL, PostgreSQL, Oracle, and more. Plus, with its free trial and free version options, there's no risk in giving it a try. Upgrade your database management game with DbVisualizer. Try it out today!

About the author

Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn