# Dynamic model generation

**URL:** <https://discourse.getdbt.com/t/dynamic-model-generation/17128>\
**Category:** Help\
**Created:** [November 26, 2024, 10:01pm UTC](https://discourse.getdbt.com/t/dynamic-model-generation/17128 "2024-11-26T22:01:47Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![kenny.tran](https://avatars.discourse-cdn.com/v4/letter/k/d26b3c/32.png) [@kenny.tran](https://discourse.getdbt.com/u/kenny.tran)\
**Post date:** [November 26, 2024, 10:01pm UTC](https://discourse.getdbt.com/t/dynamic-model-generation/17128/1 "2024-11-26T22:01:47Z")

</div>

We are exploring ways to dynamically generate SQL models in dbt. Is there a strict 1:1 requirement between a model and its `.sql` file?

Using macros, I was able to dynamically generate multiple `SELECT * FROM table_name` statements. However, I encountered the following error:

```auto
('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'SELECT'. (156) (SQLMoreResults)")

```

Here is my all\_tables.sql

```auto
{% set tables = get_table_names('SalesLT') %}

{% for table_metadata in tables %}
    SELECT *
    FROM {{table_metadata[1]}}.{{table_metadata[2]}}.{{ table_metadata[0] }}
{% endfor %}

```

and it generates this

```auto
    SELECT *
    FROM AdventureWorksLT2022.SalesLT.Address

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.Customer

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.CustomerAddress

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.Product

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.ProductCategory

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.ProductDescription

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.ProductModel

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.ProductModelProductDescription

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.SalesOrderDetail

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.SalesOrderHeader

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.vProductAndDescription

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.vProductModelCatalogDescription

    SELECT *
    FROM AdventureWorksLT2022.SalesLT.vGetAllCategories

```

---

<div class="post-metadata">

**Author:** ![bper](https://avatars.discourse-cdn.com/v4/letter/b/3ab097/32.png) [@bper](https://discourse.getdbt.com/u/bper)\
**Post date:** [December 9, 2024, 12:10pm UTC](https://discourse.getdbt.com/t/dynamic-model-generation/17128/2 "2024-12-09T12:10:40Z")

</div>

Yes, in dbt one model is one sql (or py for Python models) file.

If you want to automatically generate models you would have to develop a script/tool that automatically create those files.

There are quite a few existing examples. like this repo [GitHub - datacoves/dbt-coves: CLI tool for dbt users to simplify creation of staging models (yml and sql) files](https://github.com/datacoves/dbt-coves) or that one [GitHub - ScalefreeCOM/turbovault4dbt: Scalefree's tool to automatically generate Data Vault models for the dbt package "datavault4dbt" based on metadata.](https://github.com/ScalefreeCOM/turbovault4dbt)