0

We have a .NET c# solution that uses AliaSQL to run database migrations / to build the backend. I have a situation where we need to write some SQL Server logic that will

  • copy records from Table A to Table B based on some logic. It's not a straight copy.
  • must be triggered at a specific point in time, not by the pipeline.
  • is a one time only script.
  • team wants to deploy the script via our pipeline.

It doesn't fit the AliaSQL paradigm because we want to control when the migration is triggered. (likely a scheduled cron job) in this particular case, it will only be needed once.

Just looking for ideas / a pattern on how others handle these types of one off scripts.

Improve this question
3
  • What about a stored proc that gets created by a data migration. After being executed manually maybe, then someone adds another migration to delete the stored proc?
    – Greg Burghardt
    Commented May 24, 2023 at 19:03
  • I don't believe there is a pattern for this.
    – Greg Burghardt
    Commented May 24, 2023 at 19:03
  • @GregBurghardt: see my answer
    – Doc Brown
    Commented May 24, 2023 at 19:14

1 Answer 1

Reset to default
2

Make the script part of a stored procedure which schedules itself. You find the basic details how to write such a stored procedure in TSQL in this SO post from 2009. AliaSQL can install this SP by some script in the Everytime folder and run it immediately, but the execution time of the core parts will be controlled by the logic inside the SP, which defers its execution until a certain point in time is reached.

The SP may also remove itself once it was executed successfully, to leave the DB in a state as if the data migration was done by an ordinary SQL script. This SO post from 2014 says this is definitely possible. Or, the SP is left in the database forever, so the SP installation script tests for its existence and when the SP is already there, it assumes it was already executed. This makes the whole process idempotent.

Improve this answer
7
  • I'm very familiar with a stored proc ... but as far as scheduling itself... i see the example is on database start up. But I would like to know other trigger options. What should I google?
    – dot
    Commented May 24, 2023 at 19:07
  • @dot: the "database startup" is completely irrelevant in the example. The point is, a SP can wait for doing certain actions until a defined point in time (as you said, like a cron job). Maybe you read the SO post again?
    – Doc Brown
    Commented May 24, 2023 at 19:12
  • I was a little confused by the SO answer as well. I think the answer mentions rebooting the database so it runs the stored proc right away. If you don't reboot, I imagine the stored proc runs at the next scheduled time. Am I interpreting that correctly, Doc?
    – Greg Burghardt
    Commented May 24, 2023 at 19:17
  • @GregBurghardt: the OP mentioned AliaSQL and a pipeline. I took a short look into the manual: it is a tool for DB migrations which executes SQL scripts whenever someone says it has to. Hence installation and initial execution of the SP should be just a script in the Everytime folder used by AliaSQL.
    – Doc Brown
    Commented May 24, 2023 at 19:23
  • A stored proc that destroys itself after executing absolutely must be put in MissionImpossible.sql - this stored proc will self-destruct after completion. But then we have copyright issues...
    – Greg Burghardt
    Commented May 24, 2023 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.