Move Records to Another Table
How do you move records from one db table to another? I have a "News" table and I want to move several articles to an "archive" table...
How do you move records from one db table to another? I have a "News" table and I want to move several articles to an "archive" table...
Comments
Re: Move Records to Another Table
I can offer a simple explaination, and as far as I know, it's the easiest way to accomplish this. Be aware though, that this method will move the records one at a time, as you choose them.
Overall, we won't be "Moving" the records from table to table; we're going to Insert them into the new table and then delete them from the old table using a series of different steps.
Step 1) Build Your Page
Create a page which will build a Recordset containing only the record you wish to move. In this example, the page will receive the Unique ID of the record via the Request.QueryString Variable. We'll say the SQL for this Recordset looks like this in the DMX Recordset (Advanced View):
SELECT *
FROM tblOLD_TABLE
WHERE RecordID = varRecordID
Variables Section
Name: varRecordID
Default Value: 0
Run-time Value: Request.QueryString("RecordID")
Realize that your own recordset can be whatever you want, as long as it's going to pull only the record you wish to move. My SQL code above will take only the record whose "Record ID" matches what was passed from the previous page via the GET method.
Step 2) Custom Code
Now for the tricky part; if you can get this part right, you're home free. We're going to create some Commands (sometimes called Stored Procedures) that will execute ONLY when we want them to. Here's how:
Your page will initially load with ONLY the Recordset. On this same page we're going to create a form with a hidden field called FORM_SUBMIT, and a Submit button. Point the form (Action) to itself (the page we're working on) and use the Post method. Set the FORM_SUBMIT field equal to the RecordID in your recordset. In my example this value would be <%=(Recordset.Fields.Item("RecordID").Value)%>
Now copy the following code into your page AFTER your Recordset and BEFORE the <HTML> tag:
<%
' If the form is submitted, process this code
If Request.Form("FORM_SUBMIT") <> "" Then
' Assign values to our variables to be inserted in the new table
cmdINSERT__varRecordID = (Recordset.Fields.Item("RecordID").Value)
cmdINSERT__varOtherField01 = (Recordset.Fields.Item("OtherField01").Value)
cmdINSERT__varOtherField02 = (Recordset.Fields.Item("OtherField02").Value)
' Create a command to Insert the record into the new table form the old table
Set cmdINSERT = Server.CreateObject("ADODB.Command")
' Be sure to change "MM_Connection_STRING" to your actual Connection String
cmdINSERT.ActiveConnection = MM_Connection_STRING
' Be sure to change your table name and field names to match your case
cmdINSERT.CommandText = "INSERT INTO tblTABLE_NEW (RecordID,OtherField01,OtherField02) VALUES ('" + Replace(cmdINSERT__varRecordID, "'", "''") + "','" + Replace(cmdINSERT__varOtherField01, "'", "''") + "','" + Replace(cmdINSERT__varOtherField02, "'", "''") + "') "
cmdINSERT.CommandType = 1
cmdINSERT.CommandTimeout = 0
cmdINSERT.Prepared = false
cmdINSERT.Execute()
cmdINSERT.ActiveConnection.Close
' Assign a value to the variable to delete the old record
cmdDELETE__varRecordID = Request.Form("FORM_SUBMIT")
' Create a command to delete the old record
Set cmdDELETE = Server.CreateObject("ADODB.Command")
' Be sure to change "MM_Connection_STRING" to your actual Connection String
cmdDELETE.ActiveConnection = MM_Connection_STRING
' Be sure to change your table name and field name to match your case
cmdDELETE.CommandText = "DELETE FROM tblTABLE_OLD WHERE RecordID = '" + Replace(cmdDELETE__varRecordID, "'", "''") + "'"
cmdDELETE.CommandType = 1
cmdDELETE.CommandTimeout = 0
cmdDELETE.Prepared = True
cmdDELETE.Execute()
cmdDELETE.ActiveConnection.Close
' Once the action is complete, send the user to another page
Response.Redirect("complete.asp")
End If
%>
This simple graphic illustrates how this page will work:
The Recordset is processed first. Only when the form is submitted do the Insert and Delete functions get processed. After that, the user is redirected to a new page (in my case, "complete.asp"
Step 3) Clean Up The Code
My code is customized to my own example. You will need to go into the code and change the names of anything specific to my example. Here's a list of what needs to be changed:
1) tblTABLE_OLD - This is your old table
2) tblTABLE_NEW - This is your new table
3) RecordID - This is your unique Record ID of the record being moved
4) MM_Connection_STRING - This is your connection string to your database. It's automatically created when you build your Recordset. Just copy that one.
5) complete.asp - This is the webpage that your user is redirected to after the form is submitted
6) Recordset - If you happen to name your Recordset something else, be sure to change any reference to "Recordset" to your actual Recordset name
And probably the most complicated one(s)...
7) OtherField01, OtherField02, etc. - These are each and every field name of your record that need to be inserted into your new table. If you leave ANY of these out when you try to run this code, you will lose the data in the fields left out. You will most likely be needing to ADD more fields than what are listed in my example. Just be sure you DEFINE then and give them values, and be sure they are listed in the cmdINSERT.CommandText SQL code.
Good luck, and happy coding!
j
RE: Re: Move Records to Another Table
You must me logged in to write a comment.