Inserting data in MySQL tables

Inserting data into tables

The INSERT SQL statement impregnates our table with data. Here is a general form of INSERT.

INSERT into table_name (column1, column2....)
values (value1, value2...);

where table_name is the name of the table into which we want to insert data; column1, column2 etc. are column names and value1, value2 etc. are values for the respective columns. This is quite simple, isn’t it?

The following statement inserts the first record in employee_data table.

INSERT INTO employee_data
(f_name, l_name, title, age, yos, salary, perks, email)
values
("Manish", "Sharma", "CEO", 28, 4, 200000,
50000, "manish@bignet.com");

As with other MySQL statements, you can enter this command on one line or span it in multiple lines.

Some important points:

• The table name is employee_data
• The values for columns f_name, l_name, title and email are text strings and surrounded with quotes.
• Values for age, yos, salary and perks are numbers (integers) and without quotes.
• You’ll notice that we’ve inserted data in all columns except emp_id. This is because, we leave this job to MySQL, which will check the column for the largest value, increment it by one and insert the new value.

Once you type the above command correctly in the mysql client, it displays a success message.

mysql> INSERT INTO employee_data
    -> (f_name, l_name, title, age, yos, salary, perks, email)
    -> values
    -> ("Manish", "Sharma", "CEO", 28, 4, 200000,
    -> 50000, "manish@bignet.com");
Query OK, 1 row affected (0.00 sec)

Inserting additional records requires separate INSERT statements. In order to make life easy, I’ve packed all INSERT statements into a file. Click to download the file, employee.dat.

Once you download the file, open it in a text editor. You’ll notice that it’s a plain ASCII file with an INSERT statement on each line.

Inserting data into employee_data table with employee.dat file

On Windows

1. Move the file to c:\mysql\bin.
2. Make sure MySQL is running.
3. Issue the following command
   
   mysql employees <employee.dat

On Linux

1. Migrate to the directory that contains the downloaded file.
2. Issue the following command
   
   mysql employees <employee.dat -u username -p
3. Enter your password.