[RESOLVED] Inserting many rows with same value

Tim_Silva

Hi,

I just created a new table called article_topics with only 2 fields 'article_id' and 'topic_id'. I need to populate this table like this:

article_id (this field has to have 2,500 rows from 1 to 2500)
topic_id (this field will have the same value (1) in all 2,500 rows)

I am trying to find the SQL command to do this in phpMyAdmin.

As always,

Thanks a lot.

Tim

rulian

the best way it to use PHP or some form of server side language to produce the query you want. That's how I've populated my database with sample data before.

a piece of advice, build the query up with multiple value entries, like 250 at a time, rather then running each value insert as individual queries, it works alot faster, mysql is really built for sets of data handling rather then pragmatic programming

Tim_Silva

What I am trying to do is to find a code so that I wouldn't have to do it manually. Say:

mysql_query("INSERT INTO table1 (Col1, Col2) 
VALUES ('1,', '1')");

mysql_query("INSERT INTO table1 (Col1, Col2) 
VALUES ('2,', '1')");

mysql_query("INSERT INTO table1 (Col1, Col2) 
VALUES ('3,', '1')");

mysql_query("INSERT INTO table1 (Col1, Col2) 
VALUES ('4,', '1')");

... and so on all the way up to:

mysql_query("INSERT INTO table1 (Col1, Col2) 
VALUES ('2500,', '1')");

There must be a way of doing this without so many queries.

Tim

Sxooter

Does mysql have a generate series type function? In pgsql you'd do this:

insert into table1 select generate_series(1,2500), 1;

Not sure with mysql.

Tim_Silva

It sounded nice but a search on mysql.com for "generate_series" returned 0. So I guess it is not available.

Sxooter

Hmmmm. Looks like you might have to toss something together in php then.

Piranha

Note that you can (and should) insert many values with every query. Something like:

$sql = "INSERT INTO table1 (Col1, Col2) 
VALUES ('1,', '1'), ('2','1'), ('3','1'), ('4','1'), ('5','1'), ('6','1'), ('7','1'), ('8','1'), ('9','1'), ('10','1')";
mysql_query($sql);

Tim_Silva

Since this will be done only once, I wonder if I could dynamically generate this code and then just run it???

Sxooter

Try this:

<?php
for ($i=1;$i<2501;$i++){
        print "insert into article_topics (aticle_id,topic_id) values ($i,1);\n";
}
?>

Tim_Silva

Thanks dude,

Your code looks perfect. Too bad I didn't see it soon enough. Here is what I did.

I dynamically generated the series: ('1', '1'), ('2', '1'), ('3', '1'), ('4', '1'), ('5', '1'), ('6', '1'),... and so on up to 2500.

Then I just copied and pasted into a standard INSERT INTO command.

Ugly and unprofessional, I know, but it worked.

Thanks a lot. You guys rock.

Tim

Sxooter

Tim Silva wrote:
Ugly and unprofessional, I know, but it worked.

hey! That's my MO! hehe

rulian

as I mentioned before, not that it will be useful to you at this point, but it is faster to add value sets as opposed to individual queires, so lets say this:
$SQL="insert into article_topics (aticle_id,topic_id) values"
<?php
for ($i=1;$i<2501;$i++){
$SQL += "($i,1)";
}

mysql_query($SQL);
?>
is alot better then having each individual query run,
I learned this when trying to populate my table with 2 million table rows.
it first took over 1.5 hours to run the population,
my second attempt I build up in sets, the query took less the a minute.