Move values from one table to another

I would have a SQL stored procedure that will handle the moves based on an ID value.

first part of SQL will move the data and the second part will delete the data

How do i create a stored procedure, i am using phpmyadmin ... my hosting company is one.com

As far as I can tell, in order to create a stored procedure in PhpMyAdmin, you have to use a CREATE Procedure statement in SQL.

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

CREATE PROCEDURE MyStoredProc()
--SQL Statements ...

cheers for the link.. do you knw how i can move the values from one table to another in stored procedure?

You want to add new records to the destination, right?

And do you then want to remove them from the source?

What will be the parameters to know which rows to move?

Correct.. add new records to the destination and remove them from the original source.

I have two table contacts and mycontacts..

I want to move the following columns to mycontacts table

firstname, surname, company, email, tel, comments



Fezi

You haven't given me any parameters. This will move all the records ...

CREATE PROCEDURE MyStoredProc()
Begin
  Insert Into MyContacts(firstname, surname, company, email, tel, comments)
     (Select firstname, surname, company, email, tel, comments From Contacts 
           /*WHERE something ... */);
 
  Delete From Contacts  /*WHERE something ... */ ;
End;

Select allOpen in new window

When we get the solution, please remember to split the points w/ Emes as he put you on the right track.  :-)

hi.. what do you mean by parameters?

How do I know which records you want to move.  All of them?  If not I need to know it's "the ones with email = '123@somewhere.com'"  -- or something like that.

Emes said "based on an ID value".  Do you have an ID in the table?  That your application knows about and could provide as a parameter?  that would be ideal ...

Yes, i'm trying to move a specific record. When a user clicks on 'Add to myContacts' from the user interface, i would like the entire record to be moved to another table called 'mycontacts'.

yes, i have do have an ID column ..

Cool.  Try this:

CREATE PROCEDURE MyStoredProc(IDtoMove INT)
Begin
  Insert Into MyContacts(firstname, surname, company, email, tel, comments)
     (Select firstname, surname, company, email, tel, comments From Contacts 
           WHERE ID = IDtoMove);
 
  Delete From Contacts  WHERE ID = IDtoMove ;
End;

Select allOpen in new window

once i insert this as a stored procedure in my phpmyadmin... how do i manage this on the page? what code do i need to write on my page linking to this stored procedure

You are not wanting to [Move]
You are wanting to [Copy]

Basically, something like this:

User comes to site, and wants to add the Users Information to their List of Contacts.
So.

MyID | MyName | MyEmail | MyLink
101  | Fezi        | y@y.com | myurl.com/usersite=101

So, you want to add the record to the users information.
So, that when the User goes to their Contacts Page, they will have a list
Of all their Contacts.

Is this what you are wanting to do?
I am going to assume that it is, and will start to work on a demo for you.

Carrzkiss

regarding your other question about not wanting to move and to copy, well, i would prefer if i could actually move the entire contacts details to another table, although the copy would work fine. so your answer is YES...

I cant access the url you provided, am assuming you have started to reinstall your hard drive, please let me know when it is ok to visit the link.


Faraz

The URLs listed are good.
You should be able to go onto them.
I do not know why you cannot? Strange.

You say that you want to actually [Move] the Values from one Table to another?
That means that once you [Move] the Values, then the user will no longer have access
To his/her information no more, they will have to recreate it.

I am very confused as to why you would want to do this.
(or)
Do you have a paticular reason for wanting to do it this way?

===========
The Hard Drive was not bad, (Great!)
The cooling system under-neath my laptop, it's fans was going crazy, and it sounded just like
My HD was going bad. (Have lost 3 this past year)
But,
I did do a reinstall, and am back up and running.
(Slipstreamed a CD for my Windows XP Media Center --added SP3, Video Drivers, and a few other goodies so that I could not have a long install after the windows install, everything was done in one shot.)

Let me know about this issue.
Also.
The files are on a hosting server.
I develop on my laptop and upload to a hosting server.

have a good one.
Wayne

Hi,

This works perfectly, the reason why i wanted to move contacts, was simply because only I would use the CMS, but now that you've given me an idea of sharing contacts to add to your own list, this works perfectly. Cheers..


p.s. - please leave the files on your server till tonight,


also, before i raise a this as a question, do you know anything about uploading .csv files via asp onto the server and adds all the data in the file to the database table

Kind Regards
Faraz

You have this bit in the database, what is this bit? the stored procedure? or do i ignore? I'm using an sql server you see so will need to ammend.

INSERT INTO MyContacts ( ContactID, ContactName, ContactEmail, MemID )
SELECT Members.MemID, Members.MyName, Members.MyEmail, Members.MemID
FROM Members
WHERE (((Members.MemID)=1) AND ((Members.MyName)="Wayne Barron") AND ((Members.MyEmail)="myemail@mydomain.com") AND ((Members.MemID)=1));


Faraz

Na.
That is just what I did to create the Statement.
You do what you need to do in order to make it work for you.

=========
I will do some research on the .csv to database

Great, thank you.

always a pleasure Fezi:
Have a good one
Wayne

hi Wayne, no problem, cheers. will post a reply back soon, for my new upcoming problems, everything fine so far. :)

Fezi

I am still messing around with the other project,
Will let you know when I actually get it to work, as I am going to be starting on my Main Project (My Baby)
And that code project is going to come in very handy in it.

I will keep you informed once I get it going.