INSERT INTO table SELECT... [Resolved]

[CVMichael]

Hello...

I have two tables, one of them has only one field less than the other.
Table tblInvoiceOrderFileList has an extra ID as the first field, and the other does not.

I want to copy all records from table tblInvoiceOrdersTodayList and insert into tblInvoiceOrderFileList but with that extra ID.

In the table tblInvoiceOrdersFileList all records that I moved (at that time) will have the same ID in that extra field, and it will increment when I move the other set of records... so each set of records have different ID.
Basicly table tblInvoiceOrdersTodayList is like a queue and once a day they get moved, and the extra ID is attached to them.

I hope you got what I want...

This is what I attempted, but it did not work of course... I get a syntax error

INSERT INTO tblInvoiceOrderFileList VALUES(1, SELECT * FROM tblInvoiceOrdersTodayList)

Does anyone know how to make this work ? thanks...

[brucevde]

Make the extra field part of your Select statement.

INSERT INTO tblInvoiceOrderFileList SELECT 1, * FROM tblInvoiceOrdersTodayList

[CVMichael]

I tried that, and I get this error:

"No destination field name in INSERT INTO statement (1)."

[CVMichael]

Actually, I fixed it...

It is the way you told me, with an addition...

INSERT INTO tblInvoiceOrderFileList(ID, and the rest of the fields) SELECT 1, * FROM tblInvoiceOrdersTodayList

Thanks