Importing straight to SQL?
goravanis at gmail.com
Sun Jan 30 20:35:02 UTC 2022
Excuse me if this posted twice. I sent it the first time with the wrong sending address (not subscribed to list).
I want to import 1.7 million records into Omnis connected to postgres.
I have my postgres connection and database working now.
I tried using the COPY command in postgres, in PSQL in pgAdmin, but it comes up with too many errors on the slightest things.
I want it to blow past these errors, so I am thinking of doing the import with Omnis.
I have imported this file before, in SQLite, and to do so I used MOFF fields in the define list command. The list that imports the CSV file.
First you have to import your data into a list, then you insert the records. That is how Omnis does imports as far as I know.
My question is: Can you import into a SQL Class defined list?
If so, what then is the command structure, syntax, for doing the inserts?
Maybe this is simple, but I haven’t done it before.
So far the way I did it was I moved down the list with the usual list commands, one line at a time, moved the data from the MOFF fields into the SQL columns in my SQL list, and then did:
Do tvfcCitiesrow.$insert() Returns #F
Set current list cvCitiesList
Calculate #L as #L+1
I imagine that it would be faster if I used exec direct with an INSERT COMMAND… or even better if I used the full SQL way using an EXECUTE OMNIS command, that was loaded with an INSERT statement.. and then did Execute over and over, I think I read you can issue the command as many times as you need and it re-does the last command you put through it.
I am unfamiliar with that approach, but I theoretically understand it.
Another way to put all this is "What is the fastest way to import data into a SQL database from a text file of 1.7 million records"?
This data is to become part of a demo I am putting together. This part, I want to be a demo of speed of retrieving records from a remote database. Hence why I am setting up Postgres on my server, to accept calls in from anywhere.. I want people to be able to log onto my demo, which will be JScript remote forms, and there will be a section where they can look up cities, it will give suggestion lists, and it will show the speed of retrieving from a large database. So that’s the background for what it’s worth.
When I did it in SQLite, using the above mentioned ways, it took some 4 to 5 hours to import. I put a re-log-on every 5000 records to prevent INSERT FATIGUE.. which worked out great.
When you build lists in Omnis from a table it caps out at about 700,000 records and stops loading the list. But when you import a list it can do much more…it does my whole 1.7 million for example.. so I was able to load them all into a list from a text file, and move down them inserting them into the database. I expect to be able to do that again this time.
If it did NOT load all the text file into the import list, I would not know what to do about it. I don’t know of any commands that allow you to import say 10,000 records, process them, and then import the next 10,000. Is there any such facility?
I wish that I could use the COPY command in PSQL but it came up with errors so readily I don’t think I’ll ever make it through the file.
Thank you for any input,
More information about the omnisdev-en