How to Bulk Import into a MySQL Database
Method #2
The second method is to paste the new data into your MySQL editor (instead of uploading a text file). This can be done relatively quickly once you get the hang of it.
TIP: I keep an Excel file as a template so that all I have to do when I am e-mailed the list of account numbers is paste in the new numbers. The formulas in the cells then format the text correctly, and I have the commands saved just above. I just take my mouse, copy all of column A, then paste it into the SQL editor and I am done.
Here are the commands you would use to add a column full of strings to a database. Please note: I am importing strings, so I use single quotes around the values. I believe if you were using integers you would not want to use quotes.
INSERT INTO accounts (numbers) VALUES
('F1A0'),
('HB01'),
('T601')
Where “accounts” is the table name and “numbers” is the column name where the data will be imported. The table it will be imported into has two columns, “id” and “numbers”. Since “id” will auto-increment and is the primary key, it will generate an ID number for each new account. You can do this manually if you prefer by modifying it to the following:
INSERT INTO accounts (numbers, id) VALUES
('F1A0',''),
('HB01',''),
('T601','')...
This works for multiple columns of data. You would simply display the column titles in the order that the data is listed on your import. For example:
INSERT INTO accounts (numbers, id, company_name, contact_person, phone) VALUES
('F1A0','','Brooks Brothers','Joe Smith','212-555-1212'),
('HB01','','Talbots', 'Jane Smith', '212-555-2121'),
('T601','','Lexus', 'Jack Smith', '212-555-1122')...
Good luck – and remember – work off of a test database before you run any of these commands on your system. And backup your database before each new command is run!
Related posts:
- Search and Replace MySQL using PHPMyAdmin
- Search and Replace a Custom Field in WordPress using PHPMyAdmin
- How to Add Uploaded Media to WP-DownloadManager
- Delete Comment Spam using a Cron Job
- Aligning Navbar to both Left and Right
Comments
