sp_BlitzCache 2025-12 AI config table structure migration to 2026 formats

sp_BlitzCache_AI_Migration.sql

/* Create the new table structures: */
CREATE TABLE dbo.Blitz_AI_Providers
(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 AI_Model NVARCHAR(100) INDEX AI_Model,
 AI_URL NVARCHAR(500),
 AI_Database_Scoped_Credential_Name NVARCHAR(500),
 AI_Parameters NVARCHAR(4000),
 Payload_Template NVARCHAR(4000),
 Timeout_Seconds TINYINT,
 Context INT,
 DefaultModel BIT DEFAULT 0);

CREATE TABLE dbo.Blitz_AI_Prompts
(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 PromptNickname NVARCHAR(100),
 AI_System_Prompt NVARCHAR(4000),
 DefaultPrompt BIT;
GO


/* Migrate the data from old to new tables: */
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRAN;

    /*==============================================================
      1. Migrate providers
      Distinct on the provider-related columns from the old table.
    ==============================================================*/
    ;WITH SourceProviders AS
    (
        SELECT DISTINCT
            AI_Model,
            AI_URL,
            AI_Database_Scoped_Credential_Name,
            AI_Parameters,
			Payload_Template,
            Timeout_Seconds,
            Context,
            DefaultModel
        FROM dbo.Blitz_AI
    )
    INSERT INTO dbo.Blitz_AI_Providers
    (
        AI_Model,
        AI_URL,
        AI_Database_Scoped_Credential_Name,
        AI_Parameters,
		Payload_Template
        Timeout_Seconds,
        Context,
        DefaultModel
    )
    SELECT
        sp.AI_Model,
        sp.AI_URL,
        sp.AI_Database_Scoped_Credential_Name,
        sp.AI_Parameters,
		sp.Payload_Template
        sp.Timeout_Seconds,
        sp.Context,
        sp.DefaultModel
    FROM SourceProviders AS sp
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM dbo.Blitz_AI_Providers AS p
        WHERE ISNULL(p.AI_Model, N'') = ISNULL(sp.AI_Model, N'')
          AND ISNULL(p.AI_URL, N'') = ISNULL(sp.AI_URL, N'')
          AND ISNULL(p.AI_Database_Scoped_Credential_Name, N'') = ISNULL(sp.AI_Database_Scoped_Credential_Name, N'')
          AND ISNULL(p.AI_Parameters, N'') = ISNULL(sp.AI_Parameters, N'')
          AND ISNULL(p.Payload_Template, N'') = ISNULL(sp.Payload_Template, N'')
          AND ISNULL(p.Timeout_Seconds, 0) = ISNULL(sp.Timeout_Seconds, 0)
          AND ISNULL(p.Context, -1) = ISNULL(sp.Context, -1)
          AND ISNULL(p.DefaultModel, 0) = ISNULL(sp.DefaultModel, 0)
    );

    /*==============================================================
      2. Migrate prompts
      Distinct on the prompt-related columns from the old table.
    ==============================================================*/
    ;WITH SourcePrompts AS
    (
        SELECT DISTINCT
            PromptNickname = Nickname,
            AI_System_Prompt = AI_System_Prompt_Override
        FROM dbo.Blitz_AI
    )
    INSERT INTO dbo.Blitz_AI_Prompts
    (
        PromptNickname,
        AI_System_Prompt
    )
    SELECT
        sp.PromptNickname,
        sp.AI_System_Prompt
    FROM SourcePrompts AS sp
    WHERE (sp.AI_System_Prompt <> '')
      AND NOT EXISTS
    (
        SELECT 1
        FROM dbo.Blitz_AI_Prompts AS p
        WHERE ISNULL(p.PromptNickname, N'') = ISNULL(sp.PromptNickname, N'')
          AND ISNULL(p.AI_System_Prompt, N'') = ISNULL(sp.AI_System_Prompt, N'')
    );

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN;

    THROW;
END CATCH;
GO

/* Validation queries:

Old table:
SELECT * FROM dbo.Blitz_AI

New tables:
SELECT * FROM dbo.Blitz_AI_Providers;
SELECT * FROM dbo.Blitz_AI_Prompts;
*/