What is the easiest way to add a bunch of content to a SQL database?

Question

Nothing technical here. Suppose I have a lot of different categorized data, and I would like to create a database out of it. Would someone literally hand plug in all that info with SQL code itself? Or do some people make a mock website just to input data? What are some of your strategies?

Answer 1

If there would be no way to do it automatically, then a mock website would be the way to go: you can even use it with more people at once, actually multiplying the input speed (as long as you don't mess up assigning each of them a different part of the data).

Answer 2

What format is your data in? And how much of it is there? If its Excel then SQL Server has tools to import it in. I'm not sure if MySQL has anything similar. Even if it doesn't one other technique I have used with Excel data is to use a formula to concatenate as required to generate the INSERT statements. Then just paste those into a query window and run that.

I wouldn't do a website for it unless I was building an admin site for it already and wanted to test that with the initial load.

Answer 3

Most databases have a way to do bulk inserts or have tools for data import.

My strategies normally involve such tools.

Here is an example of importing a CSV file to SQL Server.

Answer 4

Most database servers provide a way to import data from a variety of formats, you could look into that first.

If not, you could write a simple script or console application to parse your input data, and write out a SQL script to insert the data into appropriate tables.

For example, if you data was in a CSV file, you would parse each line in the file, and generate an insert statement to write out to a .sql file.

MyData.csv
1,2,3,'Test',4
2,3,4,'Test2,6

GeneratedInsert.sql
insert into table (col1,col2,col3,col4,col5) values (1,2,3,'Test',4)
insert into table (cal1,col2,col3,col4,col5) values (2,3,4,'Test2',6)

Answer 5

Most web languages have some sort of auto-scaffolding that you can quickly set up. Useful for admin work as well, if your site is hosted without direct access to DB.

Otherwise, yeah - write the SQL statements. Useful to bring a database up as part of your build process.

Answer 6

MySQL has a statement LOAD DATA INFILE that is intended for loading bulk data from flat files. It's easy to use and much faster than alternative methods.

But first you do have to use SQL to design tables with fields that match the field of your import data. That is, if you have some file with comma-separated data:

Titanic;1997;4 stars
Batman Begins;2005;5 stars
"Harry Potter and the Sorcerer's Stone";2001;3 stars
...

You would create a table:

CREATE TABLE Movies (
  title   VARCHAR(100) NOT NULL,
  year    YEAR NOT NULL
  rating  VARCHAR(10)
);

Then load data:

LOAD DATA INFILE 'movies.txt' INTO TABLE Movies
  FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"';