Transferind big amount of data (1 Viewer)

[irade92]

Hi
I have database and tables in HostGator and it is connected trough ODBC. Everything is fine. I want to transfer several thousand records from local tables to ODBC connecetd tables. I was trying using Access append Query but it is too slow. What is the fastest way to transfer data from local tables to ODBC tables.

 

[GPGeorge]

I dislike terms like "best" and "fastest" in discussions of topics related to Access database applications because they imply there really is such a thing as "the best". Seldom can we actually apply an objective measure to such things and come up with a winner.

Moving data from a local table to a remotely hosted SQL Server table (or whatever database engine you have at HostGator) is not going to be as fast as moving data from one local table to another, for a number of reasons, including the fact that an internet connection is in between the two databases.

An append query may be actually be the most efficient and fastest way to do that, in fact. An alternative might be to use SSMA, which you can download here. Get the version for whatever database you have at HostGator.

But it would be useful to know what you mean by "too slow", which is one of those unfortunately subjective measures. How long are we talking, seconds? minutes? hours?

 

[tvanstiphout]

Assuming your back-end is SQL Server (I have NO idea why you were silent on that point), then BCP is the fastest way: https://learn.microsoft.com/en-us/sql/tools/bcp-utility.
It may not be the handiest way, or the way we would recommend if we had more info, but you gave us very little to go on.

 

[irade92]

I dislike terms like "best" and "fastest" in discussions of topics related to Access database applications because they imply there really is such a thing as "the best". Seldom can we actually apply an objective measure to such things and come up with a winner.

Moving data from a local table to a remotely hosted SQL Server table (or whatever database engine you have at HostGator) is not going to be as fast as moving data from one local table to another, for a number of reasons, including the fact that an internet connection is in between the two databases.

An append query may be actually be the most efficient and fastest way to do that, in fact. An alternative might be to use SSMA, which you can download here. Get the version for whatever database you have at HostGator.

But it would be useful to know what you mean by "too slow", which is one of those unfortunately subjective measures. How long are we talking, seconds? minutes? hours?

too slow means about two hours for 20.000 records.

 

[Isaac]

well if you have permissions on this back end system as extensive as to allow you to use bcp, you could also look into using one of several bulk load T-SQL statements. my experience with them has been [subjectively] very fast, in the rare event i'm working in a context where I have full control over the back end system. And you can still automate that from Access if desired using a pass through query

 

[irade92]

well if you have permissions on this back end system as extensive as to allow you to use bcp, you could also look into using one of several bulk load T-SQL statements. my experience with them has been [subjectively] very fast, in the rare event i'm working in a context where I have full control over the back end system. And you can still automate that from Access if desired using a pass through query

In Access there is an Export to XML, I exported table to xml on desktop, in HostGator, MyPHPAdmin there id option Import from XML, I imported from XML, and it says succsesfully imported, but I cannot see where it is imported...and of course i first chek the table where it to be imported..I can send you a pic of it if you like

 

[irade92]

In Access there is an Export to XML, I exported table to xml on desktop, in HostGator, MyPHPAdmin there id option Import from XML, I imported from XML, and it says succsesfully imported, but I cannot see where it is imported...and of course i first chek the table where it to be imported..I can send you a pic of it if you like

In Access there is an Export to XML, I exported table to xml on desktop, in HostGator, MyPHPAdmin there id option Import from XML, I imported from XML, and it says succsesfully imported, but I cannot see where it is imported...and of course i first chek the table where it to be imported..I can send you a pic of it if you like

 

[Isaac]

Apparently into:

 

[irade92]

Well...finally I found the fastest way how to transfer data from local Access table to ODBC connected table. First I export data to Excel, then from Excel I exported to OpenDocument Spreadsheet, then Import as OpenDocument Spreadsheet into ODBC table..it need only few seconds...Thanks to all that helped me.

 

[Galaxiom]

First I export data to Excel, then from Excel I exported to OpenDocument Spreadsheet, then Import as OpenDocument Spreadsheet into ODBC table..i

SQL Server can read Excel and Access files.

 

[irade92]

SQL Server can read Excel and Access files.

Here there was not such possibily..I cheked all before this step.. this is fastest way of transfering big data from local table to Odbc table on HostGator myPhpAdmin database

 

[Pat Hartman]

That's a lot of work.

If the data is going from one SQL Server table to another, then the best option would be a pass-through query. That eliminates any interaction with ODBC and ensures that the data doesn't have to go from the server to memory on the local PC and then back to the server should Access be inclined to make such a stupid execution plan.

20,000 records is not a lot in the greater scheme of things. Can you post your append query so we can see if there is a problem with it. Be sure to identify which tables are local and which are SQL server if there are more than 2 in the query.

 

[irade92]

I was using append query, one local table to another ODBC table. 20000 records transfered in about 3 hours. So what about 100.000 records?. . I was experimenting with more option as in Xml, Exel, csv but finaly did it with OpenDocument spreadsheet. I was transfering data to myPHP Admin in HostGator database..

 

[Pat Hartman]

So, the append query is a secret

 

[irade92]

So, the append query is a secret

No, but is much slower than what I explained

 

[Pat Hartman]

Well good luck. Keeping the query to yourself won't get you any useful help with it.

 

[irade92]

Well good luck. Keeping the query to yourself won't get you any useful help with it.

 

[irade92]

I am not keeping append query for myself. It is usual query generated by Access. As I said, it is about transfering data from local table ( only one table) of my PC to ODBC connected tabel. If you want it I can send you SQL lines..nothing special..data are not goig from one SQL server to another, but from my local backend to SQL server on HostGator, myPHPAdmin database conneted to my frontend with ODBC. I hope now I am much clear now..my intetion is to free from local backend everything and working only through ODBC. This allows me to connect more PC from defernt areas together.

 

[ebs17]

That sounds like a one-off event.

The biggest problem will be the low bandwidth of the connection between your web provider and your local table.

You could try the following: The combination MyPHPAdmin / MySQL knows SQLDump for backup and for reloading the backup.
- Look at the structure of such a sql file.
- Then create such a file on your local backend from your table.
- Upload this file to your web host (FTP?)
- Load the data from the file into the empty table.

 

[irade92]

All I do is folowing: export data from my local table to Excel file which is few seconds. I oppend Excell file and than Save as OpenDocument Spreadshhet on my PC. Then go to MyPHPAdmin and there is option Import. I import file OpenDocument Spreadshhet with cheked "My file has column" and again few seconds and it is done..My table is transfered as new table in Database. As many of fields are transfered as Warchars I need to set them properly, as INT, Decimal, Date and so on.. If I go regulary witn Access append query it took me few houars..