SQL move record query

Hi, if i have a record wit hthe following information

stock_Serial(primary key), stock_name, stock_type, stock_price

how would i move this to another table and then delete it from the current table via sql? I have found how to do this whereby it will move a record, after your input but what i would like is for it to move the current record being viewed in a form to be moved.

Any help greatly appreciated,

thanks!

anon
Friday, February 6, 2004

Since you said 'form', I'll assume you're working with MS Access (which I hardly know, don't kill me):
DoCmd.RunSQL "insert into stock2 ( stock2_name, stock2_type, stock2_price) values (" & Me.stock_name & "," & Me.stock_type & "," & me.stock_price & ")"
DoCmd.runsql "delete from stock where stock_Serial = " & me.stock_serial

Or something very similar.
As others will undoubtably point out, are you sure this is what you want to do? Do you have the possibility of chaning the database design so that you could have a field to the stock table denoting where your stock item is? E.g. stock_location would be a foreign key to a location table, itself holding values like 'In warehouse', 'Under way', etc.

Wow. Suddenly I'm getting this deja-vu of a thread where we all assumed the original poster was using Access and we all messed up. Anybody else remember this?

Yves
Friday, February 6, 2004

Giant gaping security hole #1: never directly construct SQL statements. Always use parameterized SQL instead, to prevent SQL injection attacks.

Brad Wilson (dotnetguy.techieswithcats.com)
Friday, February 6, 2004

To answer your initial question, as long as the 2 tables have the same layout you can simply do this sql:

insert into table2 select * from table1
where stock_serial='.....'

delete from table1 where stock_serial='....'

However, I too would like to raise the same question as the previous poster...why do you need to copy data between tables? Instead just add a status or location field that denotes the different kinds you need.

Here is an example:

TBL_CAR_OWNED          TBL_CAR_LEASED    TBL_CAR_SOLD
make: chevy                  make: chevy            make: chevy
make: buick                  make:  cadillac          make: toyota

To see how many Chevys you have that you own or lease, you need to make SQL UNIONs, like this:

SELECT * FROM CAR_OWNED
WHERE MAKE='chevy'
UNION ALL
SELECT * FROM CAR_LEASED
WHERE MAKE='chevy'

This is a tedious way to query the data, since UNIONs is a bit costly. It would be alot better to just have one table like this:

TBL_CAR
Make: chevy
Status

TBL_STATUS
Owned
Leased
Sold

This way you can query the database, using one table only.





If you have 3 tables storing the same items, you need UNIONs to create a resultset that contains say 2 of them.

Patrik
Friday, February 6, 2004

Just a little note: You should use:
CurrentDB.Execute(sqlStringGoesHere)
instead of:
DoCmd.RunSQL(sqlStringGoesHere)

because the runSQL command will come up with confirmation messages after every call ("You are about to INSERT 1 record. Is this okay?"), whereas the CurrentDB.Execute just does its thing without a peep (unless an error occurs).

Jordan Lev
Friday, February 6, 2004

thanks so much i really appreciate the help and its working fine now!

anon
Monday, February 9, 2004

This doesn't seem to be working. I am using access XP.
my table names are tblstock and tblsales,

the error message i get is "appending 1 rows" which is correct, but when i click ok, it says moved 0 rows.

The code that i am using in SQL is this:

INSERT INTO tblsales ( stock_name, stock_type, stock_price, stock_serial )
VALUES (" & Me.stock_name & ", " & Me.stock_type & ", " & me.stock_price & ", " & me.stock_serial & ");


i am going to do the add and delete queries separate via a macro, but i cant seem to get this work. Any help is greatly appreciated

anon
Friday, February 20, 2004

  Recent Topics

  Fog Creek Home