Here comes the third article in the SQLyog for MySQL series of articles. Today we will deal with the various ways you can insert data into a MySQL database or MariaDB database using SQLyog, which is a powerful MySQL manager and administration tool that can also be used to manage MariaDB database.

Previously we dealt with how to create MySQL tables the easy way with SQLyog. And before that we talked about how to backup a MySQL database using SQLyog. You might also want to know that most of these operations are compatible with MariaDB database.

At this point we will assume that you have your MySQL database server installed and SQLyog downloaded and installed. If not then you would need to download MySQL from here, and download a free copy of SQLyog from here.

For purposes of this tutorial we are going to use a single table in the `test` database that comes with the default installation of MySQL. To get started you can execute this SQL query in SQLyog to create the empty table which we will later populate.

CREATE TABLE `students` (
`sudent_id` smallint(4) unsigned NOT NULL auto_increment,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`birth-date` date default NULL,
`gender` enum(‘Female’,'Male’) NOT NULL,
PRIMARY KEY (`sudent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If you already have a table called `students` you may use a different name that makes sense to you.

1. Direct Entry Into the Table Data Tab View

The tree view on the left hand side of the SQLyog User Interface shows a list of all the databases you have on the particular server you have chosen to open. Expand the `test` database and you should see the table you have just created on the list.

With your Mouse, right-click on the `student` table icon and a context menu should appear. From that menu you should see a menu item labeled open table. Click on it and ypu should see a view of the table you created without any data as illustrated below.

SQLyog Table View

Entering data into this MySQL table quite simple. Below the column name there is a space which you can click on to start entering data. Simply type in the appropriate data from right to left and when you get to the end of the row you can click the save button just above the view to commit the data into the database. Note that you do not need to enter data into the `student_it` column as it is an auto increment column and such a column will be populated automatically by MySQL.

2. Importing CSV Using Load Local

To insert data into MySQL using this method means that you had already got this data stacked up somewhere and you need to populate the table you just created. This is a viable option for importing large amounts of data that may have originated from a different database server and you decided to migrate the data into MySQL. It is also a method you would find using in cases where you had prviously backed up your data as a CSV file and it is has come for you to restore your data back into MySQL.

To get started with Importing CSV files using Load Local, you right-click on the table icon to get a context menu. There will be a menu item labeled “Import” and a sub-menu item labeled “Import CSV Data Using LOAD LOCAL” and you will get a dialog that looks something like this:

SQLyog CSV Load Local

Here there are only two things you really need to worry about. The first is at the bottom of the dialog which is a control that allows you to choose the file you want to import. There is not much to say about this one other than it should be a CSV style formatted file.

The second thing you need to worry about is to make sure that MySQL is on the same page with this fie as far as file format is concerned. To do this there is a button on the dialog labeled “Change”. Clicking on this button opens another dialog that allows us to specify the escape characters and field delimiters to match the CSV file you are importing into the database.

If everything is in order then you can safely hit the “import” button and get your “student” table populated as expected.

3. Using the SQLyog Import External Data Wizard

This method covers those who would want to import data into MySQL primarily from other databases or storage options. You can access the wizard by going to the tree view on the left and right-clicking on the “student” table icon to give you the context menu and following the menu Item labeled “import” then selecting the first sub-menu labeled import external data. Hit the “Next” button which should give you a dialog which looks like this:

SQLyog Import Data Wizard

This wizard gives you a choice of four options to import your data into MySQL. The options include importing form Ms Access, MS Excel, CSV and more alternatives through the fourth option which allows importing through ODBC data sources.

The first three options work in the same way. You simply choose the location of the file with which you would like to import the data from. If it requires a username and password then you would need to put them in appropriately. The ODBC data source options would be similar only you would have to do this through an existing DSN that can be created using the ODBC applet that comes with your Windows Operating System.

Depending on what options you choose you will encounter additional options as you hit the “Next” button. These options include selecting directories and files themselves, choosing which fields you would like to import and so on. SQLyog provides simple and clear instructions whenever further human input is needed.

4. Using The SQL Insert Query

Finally for those traditional hard core MySQL DBAs and SQL rock stars, SQL still has something for you. Using the query editor you can still use the good old SQL INSERT statements to insert data into you MySQL tables. The only thing stopping you from choosing this option strictly boils down to whether or not this affects your productivity when working on projects.