# Sql query to 2 databases

**URL:** <https://forum.inductiveautomation.com/t/sql-query-to-2-databases/18160>\
**Category:** Ignition\
**Tags:** database\
**Created:** [May 24, 2018, 8:53pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-2-databases/18160 "2018-05-24T20:53:42Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![rigoberto.reyes](https://avatars.discourse-cdn.com/v4/letter/r/65b543/32.png) [@rigoberto.reyes](https://forum.inductiveautomation.com/u/rigoberto.reyes)\
**Post date:** [May 24, 2018, 8:53pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-2-databases/18160/1 "2018-05-24T20:53:42Z")

</div>

Hi every body.

Do you know how to do a SQL query between 2 databases?. They are stored in different database servers. I need to show the information in a bar chart.

Thanks.

---

<div class="post-metadata">

**Author:** ![zacslade](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacslade/32/3587_2.png) [@zacslade](https://forum.inductiveautomation.com/u/zacslade)\
**Post date:** [May 24, 2018, 10:09pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-2-databases/18160/2 "2018-05-24T22:09:43Z")

</div>

For the regular bar-chart, I don’t think there’s a simple way to do that (setting up a SQL Query binding on the dataset only gives the option to query a single database).

If I had to do that, my first question would be “Does the data need to be updating in real-time?” If not, then I would set up a script to fire the two queries and then concatenate the data into a single dataset on window open, and maybe on a refresh button.

If the data needs to update real-time, then I would make the same script in a Client Event Script, running at the desired update-rate (remembering that there are limits to the frequency of queries that a SQL database will tolerate, so if you have a lot of clients open and the update rate is too fast, then this can get ugly). I would encapsulate the whole Client Event Script in an “IF” statement to the effect of

```auto
if system.tag.read("[System]Client/User/CurrentWindow").value == "the window name with the bar chart":
```

In other respects, a use-case script (probably not YOUR use-case) might look something like this:

```auto
db1Results = system.db.runQuery("SELECT MYDATA FROM MYDATABASE1");
db2Results = system.db.runQuery("SELECT MYDATA FROM MYDATABASE2");

myOutputLabels = ["List","Of","Expected","Column","Names"]

myOutputData = []

for row in db1Results:
	myRow = []
	for cell in row:
		myRow.append(cell);
	myOutputData.append(myRow);

for row in db2Results:
	myRow = []
	for cell in row:
		myRow.append(cell);
	myOutputData.append(myRow);

event.source.parent.getComponent('Bar Chart').data = system.dataset.toDataSet(myOutputLabels, myOutputData)
```

I wrote that without actually using it, so I might have made a mistake or two, but you get the picture.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 24, 2018, 10:12pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-2-databases/18160/3 "2018-05-24T22:12:29Z")

</div>

Everything @zacslade said is excellent. I’ll only add two things:

1. If the number of columns (and their types) exactly match, then you can use the new [`system.dataset.appendDataset`](https://docs.inductiveautomation.com/display/DOC79/system.dataset.appendDataset) function to make the scripting substantially easier.
2. Depending on your RDBMS, it’s possible you can create a “link” between different databases (even from different vendors, ie: [https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017](https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017)). This is probably overkill for what you need, but may be worth exploring.

---

<div class="post-metadata">

**Author:** ![zacslade](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacslade/32/3587_2.png) [@zacslade](https://forum.inductiveautomation.com/u/zacslade)\
**Post date:** [May 24, 2018, 10:15pm UTC](https://forum.inductiveautomation.com/t/sql-query-to-2-databases/18160/4 "2018-05-24T22:15:05Z")

</div>

system.dataset.appendDataset looks awesome! Glad you guys added that to the toolkit 🙂