Dynamically create SQL Server tables and populate them

[coates]

Hello,

I need to load in data from different files which would represent different tables in a SQL Server environment. Is it possible to make this process dynamic? It would need to be able to create the tables and then populate them with data, running daily. Has there been a similar solution out there and what does the general design look like?

[andrew.rybka]

Hello @coates,

The solution should look like a main module that lists all the files that have to be processed and then calls a submodule for each of those files using the "Iterate" action.

The submodule should import the data from a specific file, create a DB table, and export the imported data to that table.

The tricky part here is to calculate DB types for each column. On what principle are you planning to assign column types to the exported columns?

[Sir-McNeil]

Hi @coates,

I'm an easymorph user and SQL Server DBA. Could you perhaps explain why you need to (re-)create the tables daily? If it's a daily process, that suggests that there is some regularity/similarity between the tables from day to day? The requirement to (re-)create the tables suggests you want to change something about the tables from day to day? What is that? The column names/data types? The number of columns? The table name? The table configuration (temporal table, partitions, indexes)?

Cheers,
Laurens

[dgudkov]

Linking a relevant answer:

Iterate DB table creation with database command

Hi @David_Juras, I meant creating a project that generates 'CREATE TABLE' SQL commands based on the provided dataset. These generated commands can then be executed via the 'Custom Command' command of the 'Database Command' action. Here’s an example of such a project. While the generalized version turned out to be less straightforward than I initially anticipated, I believe it’s configurable enough for you to use without requiring modifications. The project also contains the Test module with a …