How to Copy records/Data from one table to another table

[nestorvaldez]

Hi guys Im getting a problem trying to copy  a list of records from one table to another, I was investiganting and I got it:
INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1
I think I can do in this way, but the problem is the tables are not identical and I need to insert some varibles in the destination table, and a current date, and so on, so in this way I think I cant do it...

From the table I need to take the data I just need some fields, but the others are variables that are in my script..

How can I solve it.... If anybody can give a solution to this issue..

[alpine]

It really depend on the size of your origin table, if only hundred or perhaps thousand rows its no prob to run a insert query within the while loop of your select query. If you have a massive origin table i would create an insert-array from the select, add the extra info to the array and do a bulk insert.

But, aren't you able to alter the table you already have to suit your needs ?

[Barand]

You can inser the current date using CURDATE() function and also add constants to query, so if in your destination table, COL4 should be the current date and COL5 should always be 42

[code]
INSERT INTO TABLE2 (COL1, COL2, COL3, COL4, COL5 )
    SELECT COL1, COL4, COL7, CURDATE(), 42
    FROM TABLE1
[/code]