Inserting Data into Tables

Two methods: LOAD DATA or INSERT INTO

 

LOAD DATA requires a text file containing one record entry per line, with the values separated by tabs, in the order in which the columns are listed in the create table or describe directory commands:

mysql> LOAD DATA LOCAL INFILE ‘/path/to/data.txt’ INTO TABLE Students ;
Query OK, 8 rows affected, 0 warnings (0.06 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

 

*Note: If the error: “ERROR 1148 (42000): The used command is not allowed with this MySQL version” is received, the LOAD DATA capability may have been disabled. To enable it, use the following at mysql startup:¬†mysql -u root -p --local-infile=1

 

 

INSERT INTO using the manual method:

mysql> INSERT INTO Students (id, First, Surname, Age, Town, PIN, Study, Score) VALUES
(‘1’, ‘Pam’, ‘Frost’, ’21’, ‘London’, ‘1065’, ‘MySQL’, ’98’),
(‘2’, ‘James’, ‘Cross’, ’20’, ‘Watford’, ‘7519’, ‘Music’, ’88’),
(‘3’, ‘Andy’, ‘Jones’, ’34’, ‘Croydon’, ‘8198’, ‘Markets’, ’64’),
(‘4’, ‘David’, ‘Smith’, ’42’, ‘London’, ‘0151’, ‘Antiques’, ’82’),
(‘5’, ‘Julia’, ‘James’, ’17’, ‘London’, ‘2108’, ‘Maths’, ’77’),
(‘6’, ‘Phil’, ‘Watson’, ’36’, ‘Hook’, ‘9437’, ‘Cars’, ’44’),
(‘7’, ‘Terry’, ‘Waite’, ’58’, ‘Manc’, ‘2643’, ‘Music’, ’59’),
(‘8’, ‘Alex’, ‘Rose’, ’49’, ‘Cheam’, ‘0672’, ‘C’, ’70’);
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0

 

The data has now been inserted into the table, akin to:

 

 

To insert more data, simply specify the fields and their associated values:

mysql> INSERT INTO Students (First, Surname, Age, Town, PIN, Study, Score) VALUES (‘Jim’, ‘Thomson’, ‘121’, ‘Bangkok’, ‘1198’, ‘Silk’, ’96’);
Query OK, 1 row affected (0.04 sec)

*Note: In this example the ‘id’ field has NOT been included, since (having set it to auto increment) it will supplied with the primary key’s next value

Leave a Reply