INSERT SQL Table INTO Bubble data type

Need help Plugins
1

Hi everyone,

I have connected a simple bubble.io app (let’s call it ‘Bbl’) to a MS SQL database (let’s call it ‘Mssql’), using Bubble SQL database connector.

Mssql contains a table (Table1).
Bbl contains a data type (DataType1)
Both have exactly the same structure (number of fields, types, and names are matching).

I would like to write a query that copies Mssql.Table1 INTO Bbl.DataType1.
I tried
INSERT INTO DataType1 (field1, field2…)
SELECT *
FROM Table1

But this is obviously not working.
Any ideas, please? :slight_smile:

2

MySql connector doesn’t work that way. As far as I know, you can’t use SQL to insert data into Bubble (that means you can’t mix them in SQL like your example). The plugin is just makes external database available to you through queries. The rest is still on you. You will write the queries in a regular SQL and initialize the select queries for example, then you can use and do whatever you want with it.

For example, you can connect to an external database to query something:

image
image818×602 26.8 KB

Initialize the query to match bubble types, like you do in API connector. My select just returns a column called name for example:

image
image810×634 25.4 KB

Then, you can do whatever you want. I displayed them in a repeating group for example below:

image
image706×427 20.9 KB

image

3

Thank you for your very clear answer Hergin.

I am already using my data the way you suggested, and it works great indeed.

The reason I want to ‘Import’ the entire table into a DataType was to avoid having to connect to my MS SQL database everytime the user makes a change.

I wanted to:
1 _ Import the data from MS SQL into a DataType when the user starts working
2 _ Have the user work ‘locally’ in bubble to make as many changes as needed to the initial version of the table
3 _ Validate the changes, and then only, send the table back to MS SQL.

I guess I will have to change my strategy then.

Thanks again,
Dan.

4

This is the right strategy. However as I said, you can’t combine SQL tables and Bubble datatypes in one Sql query. That’s why, you have to get the database tables by using selects and whenever a data is updated in Bubble (or at a fixed time during the day), you can send the data back to Mysql by using insert/update.