Hi,

I'm trying to copy data from one table to another. I have 2 tables that are structured like this:

+-------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+----------------+
| InsertID | int(10) unsigned | | PRI | 0 | auto_increment |
| sessionID | char(48) | | MUL | | |
| productID | char(10) | | | | |
| short_description | char(48) | YES | | NULL | |
| description | char(96) | YES | | NULL | |
| quantity | char(6) | YES | | NULL | |
| retail | decimal(10,2) | YES | | NULL | |
| item_total | decimal(10,2) | YES | | NULL | |
| date | timestamp(6) | YES | | NULL | |
| weight | mediumint(8) unsigned | YES | | NULL | |
+-------------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> desc transactionDetail;
+---------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+-------+
| insertID | mediumint(8) unsigned | | PRI | 0 | |
| transactionID | mediumint(8) unsigned | | | 0 | |
| productID | char(10) | YES | | NULL | |
| quantity | char(10) | YES | | NULL | |
| retail | decimal(10,2) | YES | | NULL | |
| item_total | decimal(10,2) | YES | | NULL | |
+---------------+-----------------------+------+-----+---------+-------+

I'm trying to copy productID, quantity, retail, item_total from cart to transactionDetail and assign a transactionID that that is stored in a browser cookie. It looked like 'INSERT INTO...SELECT' would do the trick but only the first record of the first query works. Subsequent attempts to INSERT fail with a duplicate record message. Here's my SQL:

insert into transactionDetail (productID,quantity,retail,item_total)
select productID,quantity,retail,item_total from cart
where sessionID='9279001a46894f1d3220d87e40fd4f5d';

Getting the transactionID from the cookie to each record is another matter. It exists in a third table so I guess I could match it someway on the SQL server then insert it into the transactionDetail table (just fishing...). I'm programming in Perl, if anyone could offer a suggestion, it'd certainly be appreciated.

Thanks for any and all help you can provide.

Cheers,

Jeff