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

[HappyHour-SC]

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.

[Dan_Bracuk]

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.

[HappyHour-SC]

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.

[Dan_Bracuk]

How many records should have been copied?  What happens if you run that same query in access?

[HappyHour-SC]

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.

[HappyHour-SC]

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 tag attribute sales, on line 1, column 16. A CFinsert tag beginning on line 1, column 2.

The error occurred in xxxxxxxxxxxxxxxxxxxxx\sitesample\processsystembatch.cfm: line 1

1 : <CFinsert into sales(id, salesdate, provider) 2 : select id, salesdate from batchsales> 3 :

[Adam Cameron.]

Don't use <cfinsert>, as it has a specific purpose and that purpose is not what you're trying to do.

Use <cfquery>.

--

Adam

[HappyHour-SC]

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...

[Sreeindia]

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

[HappyHour-SC]

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.

[Dan_Bracuk]

You say it doesn't work.  What actually happens?

[HappyHour-SC]

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>

SQLSTATE   42000 SQL    INSERT INTO sales (partynumber,totalbill,foodsales,drinksales) select (partynumber,totalbill,foodsales,drinksales) from totalsales where 405672 = 405672 VENDORERRORCODE   -3100 DATASOURCE

[Dan_Bracuk]

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?

[HappyHour-SC]

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.

[Reed_Powell-ttnmOb]

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.

[WolgangW]

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]

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.

[WolgangW]

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