Insert from one table into another - I searched .. :(

Forum|Forum|15 years ago
July 7, 2010
2 replies
3906 views

I have a query of a table of data.. I have another identical table (same fields) that I need to transfer the all the data associated with a client ID and insert it into another table. Than, remove the data from the 1st table.

Story: a client enters transacations into a database table called "open". At the end of the day, he/she will run the query - print a report and click a "close" button that I need to take this data in "open" and insert it into "closed". table.

Thanks for your help.

Advanced techniques

This topic has been closed for replies.

W

WolgangW

Participant

So in this case the "where" should work:

<cfquery datasource="mysite" name="copytable">
    INSERT INTO WeekSales
    (Total)
    INSERT INTO TodaySales
    (phone)
    select phone from users
    select Total from TodaySales
    WHERE salesdate = todaysdate
</cfquery>

Wolfgang

Owainnorth

Inspiring

You cannot do two INSERT INTO/SELECT FROM statements in one CFQuery tag. Try this:

INSERT INTO WeekSales ( Total )

SELECT <whatever> from <table>

WHERE <something> = <something else>

</cfquery>

Then do the TodaySales

INSERT INTO TodaySales ( Total )

SELECT <whatever> from <table>

WHERE <something> = <something else>

</cfquery>

CFQuery does not work with multiple commands being run at once.

W

WolgangW

Participant

Re: Insert from one table into another - I searched ..
You cannot do two INSERT INTO/SELECT FROM statements in one CFQuery tag. Try this:
<cfquery>
INSERT INTO WeekSales ( Total )
SELECT <whatever> from <table>
WHERE <something> = <something else>
</cfquery>
Then do the TodaySales
<cfquery>
INSERT INTO TodaySales ( Total )
SELECT <whatever> from <table>
WHERE <something> = <something else>
</cfquery>
CFQuery does not work with multiple commands being run at once.

------------------------------------------------------------
Thank´s Owain for the patient correction

Wolfgang

-------------------------------------------
my sites PKV
PKV Vergleich