Specifying Tables

You can use the Azure Synapse SQL destination to write to one or more tables in a schema. The Azure Synapse SQL destination can load data into a single existing schema.

Specify the tables to use based on how many tables you want to write to:

Single table

To write data to a single table, simply enter table name as follows:

<table_name>

Multiple tables

To write data to multiple tables, specify a field in the record that defines the tables.

For example, say you have tables named after departments in your company, such as Operations, Sales, and Marketing. The records being processed have a dept field with matching values. You configure the destination to write records to the various tables using following expression: ${record:value('/dept')}.

Or, say that you want to replicate data from multiple tables in a SQL Server database. You use a JDBC Multitable Consumer origin which captures the name of each source table in the jdbc.tables record header attribute. To load data into tables based on the source table names, you use the following expression: ${record:attribute('jdbc.tables')}.

You can configure the destination to automatically create tables when a new value appears in the specified field. For example, if the dept field suddenly includes an Engineering department, the destination can create a new Engineering table for the new data. For more information, see Enabling Data Drift Handling.

When writing to multiple tables, you might also increase the number of connections that the destination uses for the write. For more information, see Performance Optimization.

Use the Table property on the Table Definition tab to specify the tables to write to.