VFP 9 & MySQL - Copy Record Same Table

Hi all. I am using VFP 9 to connect to a mysql db via odbc using sqlselect. All is working fine, I can read/write to the db and all tables. But I have a question.

I have encountered several situations where it would be nice to be able to copy the data from an existing record to a new record in the same db/table.

So, for example, say I have a record (example record_id = 100), and I want to copy all the data for this record to a new record (record_id = 101), how would I do that?


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This is probably better to do at the server side, e.g.

INSERT INTO YourTable (record_id, colName2, colName3, colName4, ...)
   SELECT 101, colName2, colName3, colName4, ... FROM YourTable WHERE record_id = 100

If the record_id column is autoincremented then you cannot list it in the column list.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Of course, you may pass above command to MySQL server from VFP by sqlexec() function call.

More info about INSERT ... SELECT: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
Olaf DoschkeSoftware DeveloperCommented:
If you'd use updatable remote views or an updatable cursoradapter, you'd be able to do all data manipulation the foxpro way inside foxpro cursors.

Eg in this case you create an updatable view of the table, parameterised to query query a certain ID, here 100, then would have that record in an updatable view cursor. You can do an INSERT similar to the one pcelba recommended but on the vfp view cursor instead of the mysql table, or you SCATTER NAME loRecord of the view record 100, creating a record object and then INSERT INTO viewcrusor FROM NAME loRecord to insert that object as a new record into the same updatable view cursor and then do TableUpdate() to write it back to the mysql database.

Once you have data in an updatable foxpro remote cursor, you can do ynything you want, anyhow you're used to do it in foxpro. This would only be a disadvantage, if you'd need to read in much data to copy into new records. As MySQL is a real server, you can make advantage of that, by just sending the sql command, which is executed on the server side. That can be a huge advantage in processing time.

On the other side, if you're displaying a record in your frontend anyway, and that process of copying is done manually by the user, working in vfp cursors is the way to go.

Bye, Olaf.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.