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
-
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
ACCESS?? 400,000 records in ACCESS?? Uhhh... Access is a desktop database. It was not designed for this kind of volume. Your not going to be able to speed this up very much at all since it works entirely in files. SQL Server is MUCH better at handling this kind of volume. If you really want to speed this up, migrate the database to SQL Server or even the MSDE first. In Access 2003, use the Upsizing Wizard... And why would you copy all the records for a month into a new table? If you are looking to speed up future queries on that new table, the time your save then is only going to be eaten up by copying everything to a new table first. So in the end, your not really saving that much time... RageInTheMachine9532
-
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'm not sure why you want to pull the entire months of tables data. I suspect that the data set will take some time to fill. You could try a paramtized query. Only fill the dataset withthe requested row. The difference is you would add "Where RecID = ? ". Your form would then have a text box for the user to enter an ID and a button to retrieve the data. Is this something that you could use?