TT380 - A bluffer's guide

Okay this is the exact procedure that I used to perform this data insert. Using SSH secure shell open a terminal window to the OU server. Change to your web directory using the following command syntax:

cd web

Log into the MySQL monitor using the following syntax, changing the jg3723 to your OU username:

mysql -u jg3723 -p

When prompted enter you OU PI number remember letters should be in CAPITALS. Select the database to be used, in this case it should be the same name as you OU username, using this command syntax:

USE jg3723;

Now its time to write the INSERT statement, for test purposes you could use the following, which is what I entered:

INSERT INTO master_name
VALUES ('',now(),now(),'Amanda','Gurney');

Hopefully, you should see the following message after pressing the ENTER key.

Query OK, 1 row affected (0.00 sec)

Well that's the INSERT method successfull used, so it's now onto the LOAD DATA method next.

With this method I pre wrote a DDL script and a plain text file that contained the data to be imported, these two files were then uploaded to my OU web directory If you would like to use my DDL script and data file click here to down load them.

Hopefully you will still have your SSH terminal window open and logged into your MySQL database. If not please connect and login to your database again.

You now need to change the DDL file to point to your own database; using Notepad open the loaddata.sql file and change the first line so it reads the name of your database. Then save the file and close it.

Now upload the DDL script and data file to your OU web directory. To check that they are there use the following syntax:

ls

Hopefully you will see the a list similar to mine below:

[jg3723@ttcfm web]$ ls
all_names.cfm  createtables.sql  loaddata.sql     mytidy.sql
contactDB.sql  eca               master_name.txt
[jg3723@ttcfm web]$ 

Okay the files are there so let's import the data. This is done by using the following syntax:

source loaddata.sql

Hopefully you will see the following messages displayed after pressing the ENTER key.

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A


Database changed

Query OK, 4 rows affected (0.00 sec)

Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

Well that's the end of my LOAD DATA and INSERT summary, you should now be able to alter the various commands and files to import any data that you wish to insert into your database. Have fun!

All the best Jayson

# posted by Jayson @ SOHO IT Solution : 2:27 pm