通过迁移更新现有表

BEGIN TRANSACTION;
-- 创建新表
CREATE TABLE new_table AS SELECT * FROM old_table;
-- 数据校验
IF (SELECT COUNT(*) FROM old_table) = (SELECT COUNT(*) FROM new_table)
BEGIN
    -- 切换表
    RENAME TABLE old_table TO old_table_backup;
    RENAME TABLE new_table TO old_table;
    COMMIT;
END
ELSE
BEGIN
    ROLLBACK;
    -- 处理数据不一致的情况
END

-- 分批迁移数据
DECLARE @BatchSize INT = 1000;
DECLARE @Offset INT = 0;

WHILE (1=1)
BEGIN
    INSERT INTO new_table (column1, column2, ...)
    SELECT TOP (@BatchSize) column1, column2, ...
    FROM old_table
    ORDER BY id
    OFFSET @Offset ROWS;

    SET @Offset = @Offset + @BatchSize;

    IF @@ROWCOUNT < @BatchSize BREAK;
END

-- 更新应用程序代码中的表名引用
-- 例如，在配置文件中修改表名
UPDATE config SET table_name = 'new_table' WHERE id = 1;

-- 检查并更新权限
GRANT SELECT, INSERT, UPDATE, DELETE ON new_table TO user1;