How do I copy data from similar tables knowing unique ID fields

[Rick Clift]

I have two tables in my database called CartItems and OrderItems. I
store all of a session's shopping cart items in the CartItems table
using the sessionID as the identifier (called cartID in my DB). After
an order is placed and is approved, I would like to copy all of the
items in the CartItems table for that given cartID to the OrderItems
table given a new orderID.

I will know the cartID and orderID ahead of time and would like to
send them both into a stored procedure and have the transfer take
place.

Example:

take this data...

CartItems (table)
--------------------------------------
cartID | itemID | quantity | price
--------------------------------------
12345 2 1 12.95
12345 7 2 17.95

and make it this data...

OrderItems (table)
--------------------------------------
orderID | itemID | quantity | price
--------------------------------------
00001 2 1 12.95
00001 7 2 17.95

via some stored procedure that I send (@cartID,@orderID)

Any help would be greatly appreciated!!

[Ray Higdon]

Not tested but something like this:

create proc cartunload @cartid int
as

insert into orderitems select cartid as orderid, itemid, quantity, price
from cartitems where cartid = @cartid

delete from cartitems where cartid = @cartid

You might think about normalizing and if it fits your needs, look at
orders and order details tables in northwind.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rick Clift]

Thanks for your reply... this would work IF the cartID and orderID could
be the same value. In my case, however, they cannot. I need to send
both the cartID and my new orderID to the stored procedure and have it
take all of the data for a given cartID and put it in the OrderItems
table with the new orderID. I tried the following based on your example
to no avail:

CREATE PROCEDURE TransferCartItems
(
@cartID int,
@orderID int
)
AS

INSERT INTO OrderItems
@orderID, Select eventID, ticketType, ticketPrice, quantity
FROM CartItems
WHERE CartID = @CartID
GO

Any ideas? This fails with a syntax error.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Rick Clift (sp*******@yahoo.com) writes:

Thanks for your reply... this would work IF the cartID and orderID could
be the same value. In my case, however, they cannot. I need to send
both the cartID and my new orderID to the stored procedure and have it
take all of the data for a given cartID and put it in the OrderItems
table with the new orderID. I tried the following based on your example
to no avail:

CREATE PROCEDURE TransferCartItems
(
@cartID int,
@orderID int
)
AS

INSERT INTO OrderItems
@orderID, Select eventID, ticketType, ticketPrice, quantity
FROM CartItems
WHERE CartID = @CartID
GO

Any ideas? This fails with a syntax error.

SELECT @orderID, eventID, ...
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> I have two tables in my database called CartItems and OrderItems. I
store all of a session's shopping cart items in the CartItems table
using the sessionID as the identifier (called cartID in my DB). After
an order is placed and is approved, I would like to copy all of the
items in the CartItems table for that given cartID to the OrderItems
table given a new orderID. <<

Have you considered that the things in the cart are just like the
things in order, but with a different LOGICAL status? Why not add
another column and not PHYSICALLY slide rows around as if they were
paper forms or punch cards?

1) There would be no way for the same item to be in both a cart and an
order, while your two-table scheme can be screwed up without really
elaborate constraints or triggers on the tables.

2) You can extend the status code to capture more information, such as
"returned to shelf from cart", or "returned to shelf from order"

3) Updating a status is faster than all that extra I/O, index
rebuilding, etc,

[Rick Clift]

Yes that does the trick... thank you very much.

Rick


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!