Dataset to Database

[mightycpa]

Hi Marcus,

I notice that when you build MS commands, you're able to accomplish things faster than when we use loops and multiple steps to do the same thing.

With regard to databases,

Where we do a DBQuery via MS, we get a recordset in an array that is organized like this:

variablename_1_1
variablename_1_2
variablename_1_3
variablename_2_1
variablename_2_2
variablename_2_3

or maybe

variablename_1_FIELD1
variablename_1_FIELD2
variablename_1_FIELD3
variablename_2_FIELD1
variablename_3_FIELD2
variablename_3_FIELD3

How difficult would it be to create a DBInsert command that would take an array structured in the same manner and insert it into the DB, rather than line by line using INSERT statements through a loop? Would that be any faster?

[Marcus Tettmar]

Well, the issue here is that the DB commands are nothing more than messengers - passing valid SQL to your database. The structure of that SQL depends on the flavour of database/driver you are using and Macro Scheduler has not control or influence over it.

Some databases - e.g. SQL Server - do support SQL insert statements that refer to a CSV or Excel file to do a "bulk insert":

https://msdn.microsoft.com/en-gb/library/ms188365.aspx

So if you're using such a database then this kind of thing already exists.

For any other DB you'd be doing a simple Insert in a loop, which you can already do.