You're very close:

INSERT INTO `$db`.`$table`
VALUES ( SELECT  '', 'author', '',* FROM `$db`.`$oldtable` )
[download]

Of course, I assume those values you mentioned were good to go in, and wouldn't cause it to clear or override defaults in the table (if it would, then you'll want to specify which fields you're inserting into by name, which takes a bit more work).

Mysql also specifically has a INSERT...SELECTsyntax that's supposed to correctly handle autonumber fields:

INSERT INTO `$db`.`$table`
SELECT  '', 'author', '',* FROM `$db`.`$oldtable`
[download]

Update: dragonchild is right. I need to stop giving Oracle answers for mysql questions. (blah...lack of subqueries)

Your second answer is the one OP is looking for, and it has nothing to do with autonumber fields; it's the correct way to build the set from the old table that should be built for each row going into the new table.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

thanks for the help, however when i try

SELECT  'test',  *  FROM  `$db`.`$oldtable`
[download]

I get

You have an error in your SQL syntax near '*  FROM  `test`.`test_table
+` at line 1
[download]

Could it be dependant upon the version of mysql?
Cheers,
Reagen

you won't get the error if you preface the * with the table name (or alias). For instance:

SELECT 'test', `$db`.`$oldtable`.*  
  FROM `$db`.`$oldtable`
[download]

works.

However, MySQL warns not to rely on column order when selecting data with select *

So you should be able to do:

INSERT INTO `$db`.`$table` 
SELECT '', 'author', '', `$db`.`$oldtable`.* 
  FROM `$db`.`$oldtable`
[download]

Well, this is what I get for not testing things first ... it seems that mysql really doesn't like that syntax, using Mysql 4.0.20 :

Read more... (3 kB)

I'm shocked, myself, that it doesn't let you put a constant first, but it does last. You can either give the entire list of fields for the insert, or we can cheat a few other ways.

If you can create a temporary table (but you'll only get nulls in the new fields ... I don't know of an equivalent to oracle's nvl() in mysql)

Read more... (1368 Bytes)

As we're quickly out of the realm of Perl, even DBI, you might want to try on a more mysql-specific forum, as they'd be more likely to know what the inner tricks are for doing this sort of thing, and there may be some other easy way to get what you want done.

And just to check that I wasn't crazy, the following works in Oracle:

SQL> select 'test',coordsys.* from soho.coordsys;

'TES ID_COORDSYS COORDSYS_NAME
---- ----------- --------------------------------------------------
test           1 Solar Disc
test           2 Heliographic
test           3 Heliocentric Ecliptic
test           4 Geocentric Inertial
test           5 Geocentric Solar Magnetosferic
test           6 Galactic
test           7 SOHOcentric Ecliptic
test          10 SDC
test          11 Solar Disc Polar
test          12 N/A
test          13 Solar Cropped

11 rows selected.
[download]