fastest way to copy a table to a new table

Hello,
Ok, I have a table with alot of accounts and I want to copy only the accounts for a month at a time to a new table.
what is the fastest and best way to do this there are like 400,000 accounts that are going to be copied and i have to only copy accounts between the startdate and enddate that the user picks from date pickers. I am using vb.net and access as the datebase. Another problem is that the date in the original table is not in a standard format 1/1/2004 it is in a Timestamp which is time since 1/1/1900 and it lookes something like this 54745186 so i have to format it before i can check it against the dates the user enters. The way i am doning it know is like this, if anyone can suggest a faster way, cause this is taking like 10 mins.

MDRDataAdapter.SelectCommand.CommandText = "Select RecID,Format([mCallEnd]![Timestamp]/1440+1,""ddddd""),
Call,Account,Billing,Client,OrgAccount,
Duration,Port,Initials,CallKind,OpTime
From mCallEnd"

MDRDataSet.Clear()
MDRDataAdapter.Fill(MDRDataSet)

For i = 0 To MDRDataSet.Tables("mCallEnd").Rows.Count - 1
tempdate = MDRDataSet.Tables("mCallEnd").Rows(i).Item("Expr1001")

If tempdate >= startdate And tempdate < enddate Then
NewMDRdap.InsertCommand.CommandText = "insert into newtable
(RecID,[Timestamp],Call,Account,Billing,Client,OrgAccount,
Duration,Port,Initials,CallKind,OpTime) values
MDRDataSet.Tables("mCallEnd").Rows(i).Item("RecID") MDRDataSet.Tables("mCallEnd").Rows(i).Item("Expr1001")
MDRDataSet.Tables("mCallEnd").Rows(i).Item("Call")
MDRDataSet.Tables("mCallEnd").Rows(i).Item("Account")
MDRDataSet.Tables("mCallEnd").Rows(i).Item("Billing")
MDRDataSet.Tables("mCallEnd").Rows(i).Item("Client")
MDRDataSet.Tables("mCallEnd").Rows(i).Item("OrgAccount")
MDRDataSet.Tables("mCallEnd").Rows(i).Item("Duration")
MDRDataSet.Tables("mCallEnd").Rows(i).Item("Port")
MDRDataSet.Tables("mCallEnd").Rows(i).Item("Initials")
MDRDataSet.Tables("mCallEnd").Rows(i).Item("CallKind")
MDRDataSet.Tables("mCallEnd").Rows(i).Item("OpTime"))

NewMDRdap.InsertCommand.ExecuteNonQuery()
End If
Next

Comments

  • : Hello,
    : Ok, I have a table with alot of accounts and I want to copy only the accounts for a month at a time to a new table.
    : what is the fastest and best way to do this there are like 400,000 accounts that are going to be copied and i have to only copy accounts between the startdate and enddate that the user picks from date pickers. I am using vb.net and access as the datebase. Another problem is that the date in the original table is not in a standard format 1/1/2004 it is in a Timestamp which is time since 1/1/1900 and it lookes something like this 54745186 so i have to format it before i can check it against the dates the user enters. The way i am doning it know is like this, if anyone can suggest a faster way, cause this is taking like 10 mins.
    :
    : MDRDataAdapter.SelectCommand.CommandText = "Select RecID,Format([mCallEnd]![Timestamp]/1440+1,""ddddd""),
    : Call,Account,Billing,Client,OrgAccount,
    : Duration,Port,Initials,CallKind,OpTime
    : From mCallEnd"
    :
    : MDRDataSet.Clear()
    : MDRDataAdapter.Fill(MDRDataSet)
    :
    : For i = 0 To MDRDataSet.Tables("mCallEnd").Rows.Count - 1
    : tempdate = MDRDataSet.Tables("mCallEnd").Rows(i).Item("Expr1001")
    :
    : If tempdate >= startdate And tempdate < enddate Then
    : NewMDRdap.InsertCommand.CommandText = "insert into newtable
    : (RecID,[Timestamp],Call,Account,Billing,Client,OrgAccount,
    : Duration,Port,Initials,CallKind,OpTime) values
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("RecID") MDRDataSet.Tables("mCallEnd").Rows(i).Item("Expr1001")
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("Call")
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("Account")
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("Billing")
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("Client")
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("OrgAccount")
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("Duration")
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("Port")
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("Initials")
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("CallKind")
    : MDRDataSet.Tables("mCallEnd").Rows(i).Item("OpTime"))
    :
    : NewMDRdap.InsertCommand.ExecuteNonQuery()
    : End If
    : Next
    :
    I've already told you twice - use a combined INSERT/SELECT query. Then it's all done on the database side. Then it doesn't have to come through all kinds of database adapters, method calls and the like. You can do date comparrision in SQL, just put it in the WHERE clause of the SELECT statement.

    You make it damm hard to help you if you don't at least respond to why people's suggestions will not work. Just ignoring them and posting more code won't get you far.

    Jonathan

    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

  • :
    : You make it damm hard to help you if you don't at least respond to why people's suggestions will not work. Just ignoring them and posting more code won't get you far.


    Jonathan,Take a deep breath and let it go s-l-o-w-l-y... :-)

  • : : You make it damm hard to help you if you don't at least respond to why people's suggestions will not work. Just ignoring them and posting more code won't get you far.
    :
    : Jonathan,Take a deep breath and let it go s-l-o-w-l-y... :-)
    :
    Ooops! I think my medication is making me more testy than I first realised...either that or the reason I'm on it.

    Jonathan

    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion