Dataset to Database

Ideas for new features & functions

Moderators: Dorian (MJT support), JRL

Post Reply
mightycpa
Automation Wizard
Posts: 343
Joined: Mon Jan 12, 2004 4:07 pm
Location: Vienna, VA

Dataset to Database

Post by mightycpa » Mon Mar 23, 2015 8:56 pm

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?
"A facility for quotation covers the absence of original thought." - Lord Peter Wimsey

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Dataset to Database

Post by Marcus Tettmar » Tue Mar 24, 2015 10:54 am

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.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts