Move data from one table to another 1

[Beracosta]

Hi everybody! I want to move every thing in a table where BasketID = "the current id". And it should all be moved to another table. My problem is that only one of the enteries in the table gets moved, not all eneteries that have the same BasketID. Any help here?

/Björn

 

[martindavey]

What SQL are you using.

 

[Beracosta]

I'm writing a ASP page (with VBScript) and the database is a Access database. i hope this answers your question.

/Björn

 

[swilliams]

INSERT INTO New_Table SELECT Temp_Table.* FROM Temp_Table WHERE BasketID = current_basket_ID

The above assumes that you have an identical structure in the two tables (moving data from Temp_Table to New_Table) and that current_basket_ID will be a variable holding the ID.
Simon

 

[Beracosta]

thanx for the help. it worked fine!

 

[Beracosta]

it worked very good. but i have another problem now... I also want a varible to be inserted into the table OrderContent. The varible should be inserted into the
Field OrderID and the MerchantID and Quantity should be moved from BasketContent to OrderContent. Here is the code sofar:

INSERT INTO OrderContent(OrderID, MerchantID, Quantity) SELECT MerchantID, Quantity FROM BasketContent WHERE BasketID = BasketID

If i take OrderID away from the code above, it works perfect, but i also want the OrderID to be inserted. Pls help me!!!

/Björn

 

[swilliams]

Presumably, the OrderID must be unique for each (obviously) and there will be multiple lines per order, so multiple lines in the OrderContent table with the same OrderID, pertaining to one particular order....

In this case, I would have a "control table", where by I hold the next order number, then I would include this table in the SELECT statement part of the INSERT query.
After inserting a record in the OrderContent table, run an update query on the control table to increment the order number.

IE:
1. create a table called ControlTBL with 2 fields; Description (text) and ItemValue (number/double).
2. Add one record to the table like so:
Description ItemValue
OrderNumber 1
3. Use the INSERT query:
INSERT INTO OrderContent(OrderID, MerchantID, Quantity) SELECT ControlTBL.ItemValue, BasketContent.MerchantID, BasketContent.Quantity FROM BasketContent, ControlTBL WHERE BasketContent.BasketID = BasketID AND ControlTBL.Description="OrderNumber"

(Note that this has not been tested, so you may have to play around with the select statement, but I think it is OK)

4. Run the query:
UPDATE ControlTBL SET ItemValue=ItemValue+1 WHERE Description="OrderNumber"

(Note that I put the extra where clause - Description="OrderNumber" - on the two queries as the ControlTBL could then be used for other things, like CustomerNumber, InvoiceNumber,....)
Simon

 

[Beracosta]

ok... i'll try it if i understand =). if i don't get it to work, could i mail the code to u so u can rewrite it like above?


Björn

 

[swilliams]

swilliams@paragon.bm
Simon