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

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