CRUD Operations in Azure Synapse

Question

I have an Azure function that fetches data from an API once daily based on the Last Modified Timestamp, and I want to store this data in Azure Synapse.

• If there is a new entry, it should create a record in Synapse.
• If an entry already exists in Synapse, it should update the record.

The database and tables are already set up in Synapse.

Answer 1

@sonal khatri - Thanks for the question and using MS Q&A forum.

To perform CRUD (Create, Read, Update, Delete) operations in Azure Synapse, you can follow these steps:

1. Create an Azure Function: This function will fetch data from your API based on the Last Modified Timestamp.
2. Connect to Azure Synapse: Use the Azure Synapse SQL pool to connect to your database and tables.
3. Insert New Records: Use the INSERT statement to add new entries to your Synapse table.
4. Update Existing Records: Use the UPDATE statement to modify existing entries based on a unique identifier (e.g., primary key).
5. Upsert Logic: Implement logic to check if a record exists. If it does, update it; if not, insert a new record. This can be done using the MERGE statement in SQL.

Here’s a basic example of how you might structure your Azure Function to handle this:

using System;
using System.Data.SqlClient;
using Microsoft.Azure.WebJobs;
using Microsoft.Extensions.Logging;

public static class UpdateSynapse
{
    [FunctionName("UpdateSynapse")]
    public static void Run([TimerTrigger("0 0 0 * * *")] TimerInfo myTimer, ILogger log)
    {
        string connectionString = Environment.GetEnvironmentVariable("SynapseConnectionString");
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            string query = @"
                MERGE INTO YourTable AS target
                USING (SELECT @Id, @Data) AS source (Id, Data)
                ON (target.Id = source.Id)
                WHEN MATCHED THEN 
                    UPDATE SET Data = source.Data
                WHEN NOT MATCHED THEN
                    INSERT (Id, Data) VALUES (source.Id, source.Data);";

            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("@Id", yourId);
                cmd.Parameters.AddWithValue("@Data", yourData);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

This example uses a MERGE statement to handle both inserts and updates. Make sure to replace YourTable, yourId, and yourData with your actual table and data variables.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.