Append queries everyday automatically

Manoharch , Use this m code

let
Source = Sql.Database("your_synapse_server", "your_database"),
NewTable = Source{[Schema="dbo",Item="NewTransactionTable"]}[Data],
ExistingTable = Source{[Schema="dbo",Item="ExistingTransactionTable"]}[Data],
AppendedTable = Table.Combine({ExistingTable, NewTable})
in
AppendedTable

Hi Manoharch,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Also, thanks to bhanu_gautam  for the prompt and helpful response.

approach to append tables dynamically with the help of Synapse Pipelines, stored procedures and metadata-driven logic to automate the process.

steps to automate: 

Create a Master Table, Use a Metadata Table to Track Loaded Tables, Create a Stored Procedure to Load New Tables then Trigger This Procedure Using Synapse Pipeline.

Alternative approach with M code

let
Source = Sql.Database("your-synapse-server", "your-db"),
TableNames = Table.SelectRows(Source{[Schema="dbo"]}[Data], each Text.StartsWith([Name], "Transactions_")),
CombinedData = Table.Combine(List.Transform(TableNames[Name], each Sql.Database("your-synapse-server", "your-db"){[Schema="dbo", Item=_]}[Data]))
in
CombinedData

**Large number of tables may slow refresh — consider aggregating in Synapse or using incremental refresh.

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & Regards,
Prasanna kumar