Query datas to add to another table :))

[freddie007]

After I query an sales table, i would like to add the outstanding unpaid to another table


Output example
id_order company amount

3 A 30
4 B 40
6 C 45



How do I add the above to another table example tbl_outstanding
(use array??? them cfinsert into the database??))

[acoder]

I wouldn't advise cfinsert use. Use cfquery instead.

Does the format of the table to be added to match the query returned?

[freddie007]

SorryI do not understand .....

I need to add the data that I have cfquery ..ed

and add this database into another table

I thought firstly I would need some array scripts
and that row by row insert into the second table

Sorry I am not clear in my explanation

[freddie007]

Sorry I do not understand .....

I need to add the data that I have cfquery ..ed from table 1

and add this data (ouput) into another table 2

I thought firstly I would need some array scripts
and that row by row insert into the second table 2

Sorry I am not clear in my explanation

[acoder]

You don't need an array. You can just loop over the query, take the values and insert them into the other table.

[freddie007]

Ok, goodidea. thanks.

U have been great!

1 more thing, comparing cfinsert statement using cfset form

to using cfquery insert,

will cfquery insert be faster if there are many record to add at the same time

[acoder]

I'm not sure about speed, but cfinsert is limited and susceptible to SQL injection attacks. cfquery is more powerful and flexible and if used in conjunction with cfqueryparam is safer.

[jKara]

After I query an sales table, i would like to add the outstanding unpaid to another table

Instead of using a query, you could also use a select. It can insert multiple records from one table to another in a single statement.

INSERT INTO OtherTable (Column)
SELECT SomeColumn
FROM FirstTable
WHERE ....

[acoder]

jKara, welcome to Bytes.

You make a good point. Thanks for your contribution.

[jKara]

jKara, welcome to Bytes.

You make a good point. Thanks for your contribution.

Thanks!

(...this extra text is because messages must be at 20 characters)