T-SQL 利用SQL语句动态生成解构并插入表格内容示例

Bertha 。 2022-07-15 10:14 153阅读 0赞

T-SQL 中的SQl语句动态生成是一个强有力的工具，他能够实现在运行时才能决定SQL语句的生成与执行。在该示例中，我们将展示利用SQL语句的动态生成实现 数据表的解构，并将数据分解插入到数据库中。

在该示例中，我们利用给定数据表（类excel）去初始化数据库。数据表的各项信息存储在数据库的不同表格里。在该示例中我们需要初始化 特定部门、特定子职位的员工数。

GO
    
    CREATE TABLE #SubPositionBreakDown ( NAME VARCHAR(50) ,D1 INT ,D2 INT ,D3 INT ,D4 INT ,D5 INT ,D6 INT ,D7 INT );
    -- 初始化数据表，可以利用Excel公式生成。
    INSERT INTO #SubPositionBreakDown VALUES ('SubPosition1', NULL, NULL, 10, 25, 48, 4, 50), ('SubPosition2', NULL, 25, NULL, NULL, NULL, NULL, NULL), ('SubPosition3', NULL, 25, 85, 59, 33, 2, 8), ('SubPosition4', NULL, NULL, 5, 5, 5, 5, 3), ('SubPosition5', NULL, NULL, NULL, NULL, NULL, NULL, NULL), ('SubPosition6', NULL, NULL, NULL, 10, 13, 2, 35), ('SubPosition7', NULL, 25, NULL, NULL, NULL, NULL, NULL), ('SubPosition8', 100, 25, NULL, NULL, NULL, NULL, NULL);
    
    DECLARE @DepartmentMappings TABLE (
        DepartmentName VARCHAR(50)
        ,MapName VARCHAR(50)
        );
    --将数据表的部门信息 映射到数据库的部门信息
    INSERT INTO @DepartmentMappings VALUES ('Department1', 'D1'), ('Department2', 'D2'), ('Department3', 'D3'), ('Department4', 'D4'), ('Department5', 'D5'), ('Department6', 'D6'), ('Department7', 'D7');
    
    --利用游标来生成 职位-部门，映射数据。
    DECLARE cur CURSOR
    FOR
    SELECT MP.DepartmentID ,SP.Id AS SubPositionId ,SP.NAME ,DM.MapName FROM #SubPositionBreakDown SB INNER JOIN dbo.SubPositions SP ON SB.NAME = SP.NAME CROSS JOIN @DepartmentMappings DM INNER JOIN dbo.Departments D ON D.DepartmentName = DM.DepartmentName DECLARE @DepartmentId INT;
    DECLARE @SubPositionId INT;
    DECLARE @SubPositionName VARCHAR(50);
    DECLARE @MapName VARCHAR(50);
    DECLARE @tempValue DECIMAL;
    
    OPEN cur;
    
    FETCH NEXT
    FROM cur
    INTO @DepartmentId
        ,@SubPositionId
        ,@SubPositionName
        ,@MapName;
    
    WHILE (@@FETCH_STATUS = 0)
    BEGIN DECLARE @sql NVARCHAR(200);
        --动态生成的SQl 语句
        SET @sql = 'SELECT @tempValue=' + QUOTENAME(@MapName) + ' FROM ' + QUOTENAME('#SubPositionBreakDown') + ' WHERE Name=''' + CAST(@SubPositionName AS CHAR(50)) + '''';
        --执行该动态生成的语句
        EXEC sp_executesql @sql
            ,N'@tempValue INT OUTPUT'
            ,@tempValue OUTPUT
    
        IF (@tempValue IS NOT NULL)
        BEGIN IF NOT EXISTS ( SELECT 1 FROM dbo.SubPositionbreakdown WHERE DepartmentId = @DepartmentId AND SubPositionId = @SubPositionId ) BEGIN INSERT INTO dbo.SubPositionbreakdown ( DepartmentID ,SubPositionId ,Weighting ,DateCreated ) VALUES ( @DepartmentId ,@SubPositionId ,@tempValue ,GETDATE() );
            END END FETCH NEXT FROM cur INTO @DepartmentId ,@SubPositionId ,@SubPositionName ,@MapName;
    END CLOSE cur;
    DEALLOCATE cur;
    DROP TABLE #SubPositionBreakDown;
    
    GO