Synapse Serverless SQL Pool

Question

Very quick question,

I am trying to find the limits of what I can and can't do with the serverless SQL Pool.

Can you do type 2 uploads in the Serverless SQL Pool?

E.g.

PersonKey PersonID PersonName StartDate EndDate MostRecentFlag

1 244 Sue Smith 2021-01-01 2022-02-23 0

1 244 Sue Jones 2022-02-23 0

And if so are there any good resources that explain how to do it?

Answer 1

I will have a look at that. I am really struggling with the idea that I usually create Stored procedures to run the data into the SQL DB Tables and with the serverless pool you don't have to.

But if you can do a merge, it feels like this is definitely something that would do in a Stored Procedure. Which is then run via a pipeline.

Answer 2

Hello @Debbie Edwards

Did you try using MERGE statement ?

MERGE INTO dbo.Person AS target
USING (VALUES
    (244, 'Sue Jones', '2022-02-23')
) AS source (PersonID, PersonName, EndDate)
ON target.PersonID = source.PersonID
WHEN MATCHED AND target.MostRecentFlag = 1 THEN
    UPDATE SET target.MostRecentFlag = 0, target.EndDate = source.EndDate
WHEN NOT MATCHED THEN
    INSERT (PersonID, PersonName, StartDate, EndDate, MostRecentFlag)
    VALUES (source.PersonID, source.PersonName, GETDATE(), source.EndDate, 1);

You can find more information about MERGE statement in the official documentation.

I hope this helps! Let me know if you have any other questions.