As you know, it’s not possible to execute SQL in the SaaS version of Business Central. “It’s to preserve the database!” someone will say. “It’s a serious lack!” I say.
Anyway, I recently need to update thousands of rows in the SaaS, merging results from several tables. A typical activity that takes not more than 5 minutes with SQL, but it’s a mess in the SaaS.
Out of desperation 😅 I put together my “Bulk Import” Codeunit and SQLite…
Step 1 – OAuth Autenthication
First thing, I wanted a tool that was easy to use in any environment, simply asking URL, login and password.
There is a “special” Client ID used by Power Shell that it’s always registered in any tenant:
1950a258-227b-4e31-a9cf-717495945fc2With this Client ID you can initiate OAuth Authentication with the common popup, embedding it in your application:
Step 2 – Download the records
Since it’s not possible to execute SQL remotely, you need to download the records that you want to manage. My “Bulk Import” codeunit returns all the available tables, their schema and, eventually filtered, rows.
Tables schema are used to replicate locally the data structure in a SQLite database. The records are downloaded and added to the database. For each table, several triggers are created locally to track the changes.
At this point, executing SQL locally seems like sending a rocket to the moon! 😅
Step 3 – Updating records
Simply run the SQL script that you want! The only limit is SQLite because I wanted a portable tool, without any database on the backend.
Step 4 – Upload modified data
All the INSERT, MODIFY or DELETE executed locally are tracked.
At the end, you can upload in to the SaaS the modified data. “Bulk Import” Codeunit will do the rest! 😅
Get this tool
Let me do some more tests 😅 and I will share this tool for free as soon as possible!