id: sqlserver_query
namespace: company.team

tasks:
  - id: select
    type: io.kestra.plugin.jdbc.sqlserver.Query
    url: jdbc:sqlserver://localhost:41433;trustServerCertificate=true
    username: "{{ secret('SQL_USERNAME') }}"
    password: "{{ secret('SQL_PASSWORD') }}"
    sql: select * from source
    fetchType: FETCH

  - id: generate_update
    type: io.kestra.plugin.jdbc.sqlserver.Queries
    url: jdbc:sqlserver://localhost:41433;trustServerCertificate=true
    username: "{{ secret('SQL_USERNAME') }}"
    password: "{{ secret('SQL_PASSWORD') }}"
    sql: "{% for row in outputs.select.rows %} INSERT INTO destination (year_month, store_code, update_date) values ({{ row.year_month }}, {{ row.store_code }}, '{{ row.date }}'); {% endfor %}"