Updating sqlite with values from MSSQL db

[Jewtus]

I'm trying to figure out a couple things and I'm not sure what the best way to go is.

First I'm trying to create a sqlite DB with a csv file, but I also need to grab data from a MS SQL server associated with the csv file and associate them. Is there any examples someone can point me to so I can understand what I'm getting into here?

I'd like to create a separate table with info associated with the csv and import them both to a SQLite DB.

[jchd]

You can use the command-line tool (sqlite3.exe) with the .import command to import .csv data into an sqlite table. This may not build the most suitable schema for your application but at least you should have the data there ready for manipulation, or transfer to another table created by you later having the right definition.

Now in your applicative code, you can use ADO support (search ADO in the example forum) to open ths MSSQL source and query it to grab data, which you can then insert/merge or otherwise process as needed.

[Jewtus]

Those are actually what I've been researching but I'm trying to find some examples that I can work from. My expertise is really more in SQL than general scripting. I have been able to figure out the connection string stuff with ADO but I'm not sure how to actually execute the query or save the executed data into an array so I can process it into the sqlite DB (I'm also not sure if it makes sense to save the data or just display it, so I'm weighing my options).

[jchd]

I'll try to work out somehow soon a (very generic) skeleton to help you figure out how to get started. Of course, lacking more specific guidance, it won't follow your actual needs accurately.

[Jewtus]

Much appreciated. Once I understand how it actually functions, I will likely ask more questions and be more specific. I'm not really even sure if its worth loading to a DB (I need to run through about 50k records and use the results, but I don't need to save the results... just query, fill in info, and delete the query data).

[jchd]

For the record, SQLite can handle disk-based or memory-based DBs. Of course a memory DB is way faster to populate, query, update... but it's temporary unless backed up to disk. I'll point you to an easy way to copy an SQLite DB while it's being used to/from disk or memory. 50k rows is a very small DB. This little thing is *very* flexible.

[Jewtus]

That would be great. I didn't think I needed a DB (I have to find out if anyone actually cares if I keep logs or a DB to track the system). If I can process it via memory (even if it is just partial data that I log), that would be perfect.

I just need to get a functional design setup and I can flush out the details after the fact. 

[jchd]

Exploring ever increasing logs is a pain. A simple DB is certainly more efficient, but up to you.

You have a number of CSV to/from Array examples (use the search) floating around. I posted one with extended format which preserves basic (and even not so basic) datatypes but it uses a somehow precise format.

I run out of time right now. I'll be back to you late in (my) evening.

[Jewtus]

No rush. I'm just exploring my options at this point. I have a feeling I will need to log the input data (into a DB simply for trace-ability) but the data that I'm pulling using ADO doesn't need to be replicated to a different database.

Thanks again! 

PS I've been reading up on ADO on w3school (because they have examples that you can try realtime) but they syntax seems significant enough for me to be completely lost. I'm also running into weird issues with my import statement (works when I execute it via command line but when I use autoit, it doesn't import the data correctly). I'll post another thread if I end up going with the cmd line sqlite (I'll probably need to pack it in with the EXE so I don't have to configure environment variables on deployment.. I was hoping to use Autoit's SQLite connection, but that seems a bit out of reach until I get more versed in Autoit scripting)