[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

Move values from one table to another

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?
0
Fezi
Asked:
Fezi
  • 12
  • 7
  • 6
  • +1
1 Solution
 
EmesCommented:
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

0
 
FeziAuthor Commented:
How do i create a stored procedure, i am using phpmyadmin ... my hosting company is one.com
0
 
Daniel WilsonCommented:
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 ...

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
FeziAuthor Commented:
cheers for the link.. do you knw how i can move the values from one table to another in stored procedure?
0
 
Daniel WilsonCommented:
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?
0
 
FeziAuthor 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
0
 
Daniel WilsonCommented:
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

0
 
Daniel WilsonCommented:
When we get the solution, please remember to split the points w/ Emes as he put you on the right track.  :-)
0
 
FeziAuthor Commented:
hi.. what do you mean by parameters?
0
 
Daniel WilsonCommented:
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.
0
 
Daniel WilsonCommented:
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 ...
0
 
FeziAuthor 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'.
0
 
FeziAuthor Commented:
yes, i have do have an ID column ..
0
 
Daniel WilsonCommented:
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

0
 
FeziAuthor 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
0
 
Wayne BarronCommented:
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
0
 
Wayne BarronCommented:
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
0
 
FeziAuthor 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
0
 
Wayne BarronCommented:
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
0
 
FeziAuthor 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
0
 
FeziAuthor 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
0
 
Wayne BarronCommented:
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
0
 
FeziAuthor Commented:
Great, thank you.
0
 
Wayne BarronCommented:
always a pleasure Fezi:
Have a good one
Wayne
0
 
FeziAuthor Commented:
hi Wayne, no problem, cheers. will post a reply back soon, for my new upcoming problems, everything fine so far. :)

Fezi
0
 
Wayne BarronCommented:
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now