We help IT Professionals succeed at work.

Move values from one table to another

Fezi
Fezi asked
on
Medium Priority
624 Views
Last Modified: 2012-05-06
Hi there,

I need some code in asp (vbscript) that allows me to move values from one table to another table.

I have two tables contacts and mycontacts. when i click on the button 'Move' from my user interface the values from the contacts table i want to move to mycontacts table.

Is this possible?

All i have at the moment is a simple Insert into statement, Can anyone be able to do this?
Comment
Watch Question

Commented:
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

Author

Commented:
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 ...

Author

Commented:
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?

Author

Commented:
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;

Open in new window

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

Author

Commented:
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 ...

Author

Commented:
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'.

Author

Commented:
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;

Open in new window

Author

Commented:
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
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
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
Author, Web Developer
CERTIFIED EXPERT
Top Expert 2009
Commented:
Here you go (Hopefully you will be able to understand everything, as I am about to loose my Hard drive in my Laptop, so I have to backup and reinstall. 120gb and about 75% is used, a lot of backup to do)

I will be back around sometime tomorrow when I have reinstalled the system onto the new HD.

Here is the page.

This is the Members List page
http://www.pcitdad.com/Test/EE/Q_24126049/MemberList.asp
The Default ID of "You" is [1]
<input type="hidden" name="MemID" value="1" />

You click on each Members name, and view their infomation.
Then [Submit] to add to you Contacts list.

http://www.pcitdad.com/Test/EE/Q_24126049/Q_24126049.zip

Enjoy.
I certainly did, makes me want to start back working on my clone site.

have a good one, I am off to re-do this system.

Wayne

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

Author

Commented:
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

Author

Commented:
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
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

Author

Commented:
Great, thank you.
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
always a pleasure Fezi:
Have a good one
Wayne

Author

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

Fezi
Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.