Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
The syntax you want is:
insert into sometable
(field1, field2, etc)
select value1, value2, etc
from wherever
In you specific case, why don't you have just one table? You could have a field called status that you simply update.
Copy link to clipboard
Copied
Thanks Dan. (that's one of my best friends name)
I do have a field for an "update" but I need to send this data over to a table to export for another client/owner.
<insert into possales (library_id, salesdate, total)
select library_id, salesdate, total from todaysales>
After I sent the data over to this .cfm page, I checked the database and it wasn't updated or data inserted.
FYI: I am using an access database too.
Copy link to clipboard
Copied
How many records should have been copied? What happens if you run that same query in access?
Copy link to clipboard
Copied
No records were copied. I am just wondering if I have the wrong statement. This gets frustrating.. Seriously, what is the big deal with an insert statement. I used a CF insert instead of just a insert.
Copy link to clipboard
Copied
I actually was using a insert and I put a CFinsert and got this error:
Invalid token ( found on line 1 at column 25. | |
The CFML compiler was processing:
| |
The error occurred in xxxxxxxxxxxxxxxxxxxxx\sitesample\processsystembatch.cfm: line 1 | |
1 : <CFinsert into sales(id, salesdate, provider) 2 : select id, salesdate from batchsales> 3 : |
Copy link to clipboard
Copied
Don't use <cfinsert>, as it has a specific purpose and that purpose is not what you're trying to do.
Use <cfquery>.
--
Adam
Copy link to clipboard
Copied
Guys: I have to be an idiot. I cannot seem to get this straight. Can someone just correct this code I have PLEASE !!
From table called TodaySales and Insert data in WeekSales
I have a record called Total in both tables. So how do I do this?
Thanks guys and sorry to be "begging" for this...
Copy link to clipboard
Copied
Hi
Try out this and let me know
<cfquery datasource="mysite" name="copytable">
INSERT INTO WeekSales
(Total)
select Total from TodaySales
</cfquery>
replace datasource name "mysite" with your datasource name
thanks
Copy link to clipboard
Copied
Thanks... this did work... BUT... can I use a WHERE statement with this??
In other words: can I do a insert for a specific sales date? Kinda like this but it doesn't work:
<cfquery datasource="mysite" name="copytable">
INSERT INTO TodaySales
(phone)
select phone from users
WHERE salesdate = todaysdate
</cfquery>
One of these tables will have all of the sales but I don't want to take ALL of the sales in this table and insert into another table.
Copy link to clipboard
Copied
You say it doesn't work. What actually happens?
Copy link to clipboard
Copied
Error Executing Database Query. | ||||||||
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression '(partynumber,totalbill,foodsales,drinksales)'. | ||||||||
The error occurred in\sitesample\processsystembatch.cfm: line 9 | ||||||||
7 : select (partynumber,totalbill,foodsales,drinksales) from foodsales 8 : 9 : where #our_id# = #our_id# 10 : 11 : </cfquery> | ||||||||
|
Copy link to clipboard
Copied
I'm sure you noticed the logic error in your where clause, but, looking for the syntax error, what happens if you run this?
select (partynumber,totalbill,foodsales,drinksales) from totalsales where 1=2
What happens if you try it without the brackets?
Copy link to clipboard
Copied
I used the # # for the varibles past. I did a where 123 = 123
and I get the same results. I didn't realize what I was trying to do would be so difficult.
Copy link to clipboard
Copied
You don't need the parens around the list of fieldnames in the SELECT part of the statement. Yes, you can put a WHERE clause at the end of the whole thing.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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