Copy data from one table to another

[Jeppe Sommer]

Hello, I want to copy some data from one table (orderpaymentno) to another table (order) in mySQL. The two tables are not identical.

I also have to predefine some data in the new table. This is the SQL query I have tried out but it doesn�t work.

INSERT INTO orderpaymentno ( 2, 2, 'Afdeling', 2, paymentNoID, paymentNo, paymentNoName, orderID ) SELECT `order`.`paymentNoID`, `order`.`paymentNo`, `order`.`paymentNoName`, `order`.`orderID` FROM `order` WHERE `order`.`companyID`=116

Can anyone help me correcting the SQL query ?
[ February 19, 2007: Message edited by: Jeppe Fjord ]

[Jan Cumps]

In the INSERT INTO clause, you can only put column names.
If the values 2, 2, 'Afdeling (I understand that!)' and 2 need to be fixed values, then you must put their column name in the INSERT INTO clause, and their value in the SELECT clause:

insert into orderpaymentno
(
  aColumnThatNeeds2
, anOtherColumnThatNeeds2
, department
, yetAnotherColumnThatNeeds2
, paymentNoID
, paymentNo
, paymentNoName
, orderID
)
select
  2
, 2
, 'Afdeling'
, 2
, paymentNoID
, paymentNo
, paymentNoName
, orderID
from 
  order
where
   order.companyID = 116

Regards, Jan

[Jeppe Sommer]

Thank you :-)