Table Loops DB Table Selector from Microsoft SQL Connector

[Frank2323]

Hello,

is it possible to select and read via a loop from a SQL Server more than one tables?

The SQL Database contain a lot of tables and with the DB table selector, I can select one table only.
Afterwards I want to write all tables to a CSV table to store the data on the sharedrive. I want to select and read around 10 tables. How can I do that with a loop?

Thank you.

[JanDuo]

Hi @Frank2323

Yes it’s possible to make a full download of your database (although I think there might be better ways to make a backup ).

For one or the other reason I never use these Microsoft SQL Server Connector nodes, but always the DB Connector.
The trick is to use the system data of your SQLserver database to get a list of the tables. This is done by the next query.

SELECT s.name as TABLE_SCHEMA
     , t.name as TABLE_NAME
     , ep.value as definition -- if you use definitions on your tables in extended properties this gives the info
from sys.schemas as s 
inner join sys.tables as t 
   on (t.schema_id = s.schema_id)
left join sys.extended_properties as ep 
  on (ep.major_id = t.object_id
      and ep.minor_id = 0 
      and ep.name = 'MS_description'
  )

If you want to avoid a full download of all tables, you either tweak the above query or apply some filtering on the result before providing it to the loop.

In the uploaded workflow I have added a “top 1” in the DB Query Reader node used within the loop (because I didn’t want to do a full download). Just removed it fordownload of complete tables. The csv-files are stored in the data directory in the workflow.

Edit: noticed I had some of the data within this workflow. Removed the data directory and uploaded again.
38388.knwf (23.8 KB)

[mlauber71]

@Frank2323 I have created a general example. If the example by @JanDuo specific to the MS SQL sever is good you are set up

Identify tables in you database(s) and loop over them and store the data in a...

If your data is very large you could also writ it out in chunks and append it to an existing CSV file (the example below). Or you could use Parquet file format.