动态数据映射的存储过程

Question

我知道这个问题可能已经被解决了，但是我很难找到解决方案。我在谷歌和这里尝试搜索过：存储过程动态数据映射插入映射表。

我有一个叫做 DataMapping 的表：

"OriginalColumn","OriginalTable","NewColumn","NewTable"

作为列名所示，此表将包含有关如何将一个表中的数据加载到另一个现有表中的元数据。

我想编写一个存储过程来发出：

select *  
from DataMapping 
where OriginalTable = XXXX 

然后使用返回的信息动态创建并执行一个基于OriginalColumn到NewColumn的映射的Insert into NewTable语句。

---

以下是生成示例问题的代码：

/****** Object:  Table [dbo].[DataMapping]    Script Date: 7/23/2018 11:34:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataMapping](
    [OriginalColumn] [sysname] NOT NULL,
    [OriginalTable] [sysname] NOT NULL,
    [NewColumn] [sysname] NOT NULL,
    [NewTable] [sysname] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[destinationTable]    Script Date: 7/23/2018 11:34:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[destinationTable](
    [id] [int] NULL,
    [field1] [nvarchar](50) NULL,
    [field2] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TableA]    Script Date: 7/23/2018 11:34:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableA](
    [sourceID] [int] NULL,
    [sourceField1] [nchar](10) NULL,
    [sourceField2] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TableB]    Script Date: 7/23/2018 11:34:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableB](
    [sourceID] [int] NULL,
    [sourceField1] [nchar](10) NULL,
    [sourceField2] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[DataMapping] ([OriginalColumn], [OriginalTable], [NewColumn], [NewTable]) VALUES (N'sourceField1', N'TableA', N'field1', N'DestinationTable')
GO
INSERT [dbo].[DataMapping] ([OriginalColumn], [OriginalTable], [NewColumn], [NewTable]) VALUES (N'sourceField2', N'TableA', N'field2', N'DestinationTable')
GO
INSERT [dbo].[DataMapping] ([OriginalColumn], [OriginalTable], [NewColumn], [NewTable]) VALUES (N'sourceID', N'TableA', N'id', N'DestinationTable')
GO
INSERT [dbo].[DataMapping] ([OriginalColumn], [OriginalTable], [NewColumn], [NewTable]) VALUES (N'sourceField1', N'TableB', N'field1', N'DestinationTable')
GO
INSERT [dbo].[DataMapping] ([OriginalColumn], [OriginalTable], [NewColumn], [NewTable]) VALUES (N'sourceField2', N'TableB', N'field2', N'DestinationTable')
GO
INSERT [dbo].[DataMapping] ([OriginalColumn], [OriginalTable], [NewColumn], [NewTable]) VALUES (N'sourceID', N'TableB', N'id', N'DestinationTable')
GO
INSERT [dbo].[destinationTable] ([id], [field1], [field2]) VALUES (NULL, N'col1      ', NULL)
GO
INSERT [dbo].[destinationTable] ([id], [field1], [field2]) VALUES (NULL, N'col2      ', NULL)
GO
INSERT [dbo].[destinationTable] ([id], [field1], [field2]) VALUES (NULL, N'col1      ', N'12345ABC')
GO
INSERT [dbo].[destinationTable] ([id], [field1], [field2]) VALUES (NULL, N'col2      ', N'24681DEF')
GO
INSERT [dbo].[destinationTable] ([id], [field1], [field2]) VALUES (1, N'col1      ', N'12345ABC')
GO
INSERT [dbo].[destinationTable] ([id], [field1], [field2]) VALUES (2, N'col2      ', N'24681DEF')
GO
INSERT [dbo].[TableA] ([sourceID], [sourceField1], [sourceField2]) VALUES (1, N'col1      ', N'12345ABC')
GO
INSERT [dbo].[TableA] ([sourceID], [sourceField1], [sourceField2]) VALUES (2, N'col2      ', N'24681DEF')
GO
INSERT [dbo].[TableB] ([sourceID], [sourceField1], [sourceField2]) VALUES (2, N'B1234     ', N'9999')
GO
INSERT [dbo].[TableB] ([sourceID], [sourceField1], [sourceField2]) VALUES (2, N'B5678     ', N'9999')
GO

最终解决方案

这是最终解决方案，我已经采用下面最佳答案并将其转化为存储过程，使得我可以选择要填充哪个目标表。在您不想重新运行整个导入过程的情况下使用。

CREATE PROCEDURE [dbo].[DataMappingProc2]
(
@DestinationTable as VARCHAR(40)
)
AS 
    BEGIN
    DECLARE @Sql nvarchar(max) = ''

    SELECT @Sql += 'INSERT INTO '+ NewTable +'(' + 
            STUFF((SELECT ', ' + NewColumn
                   FROM dbo.DataMapping t1
                   WHERE t1.NewTable = t0.NewTable
                   AND t1.OriginalTable = t0.OriginalTable
                   And t1.NewTable = @DestinationTable
                   ORDER BY NewColumn
                   FOR XML PATH('')), 1, 2, '') +') ' + 
            'SELECT '+ 
            STUFF((SELECT ', ' + OriginalColumn
                   FROM dbo.DataMapping t2
                   WHERE t2.NewTable = t0.NewTable
                   AND t2.OriginalTable = t0.OriginalTable
                   And t2.NewTable = @DestinationTable
                   ORDER BY NewColumn
                   FOR XML PATH('')), 1, 2, '') +' FROM '+ OriginalTable +'; '
    FROM dbo.DataMapping t0
    WHERE t0.NewTable = @DestinationTable
    GROUP BY NewTable, OriginalTable

    EXEC (@Sql)

    Return 0
    END
GO

运行存储过程

DECLARE @return_value int
EXEC    @return_value = [dbo].[DataMappingProc2]
        @DestinationTable = N'DestinationTable'

SELECT  'Return Value' = @return_value
GO

- Reddspark

---

过程式代码非常依赖于特定的供应商 - 因此，请添加一个标签来指定您是否正在使用 mysql，postgresql，sql-server，oracle 或 db2 - 或完全不同的其他内容。 - marc_s

完成。添加了 SQL Server。 - Reddspark

使用映射表提前生成一组视图，然后只需使用这些视图如何？ - David Browne - Microsoft

你能提供样本数据吗？以及构建的查询的动态输出。 - Shakeer Mirza

现在问题包含了适当的样本数据，我已经更新了我的答案。我认为你应该会发现它是解决你问题的合适方案。 - Zohar Peled

Answer 1

更新： 在撰写我的原始答案时，我错误地假设每个表和列对之间会有唯一的映射（如果原始问题包含现在包含的示例数据，该假设将被避免）- 因此我的答案是错误的。

现在问题已经更新，包括适当的示例数据，我可以通过向子查询添加另一个条件和在原始查询中添加分组来更新我的答案，我已经成功地得到了一个解决方案：

DECLARE @Sql nvarchar(max) = ''

SELECT @Sql += 'INSERT INTO '+ NewTable +'(' + 
        STUFF((SELECT ', ' + NewColumn
               FROM dbo.DataMapping t1
               WHERE t1.NewTable = t0.NewTable
               AND t1.OriginalTable = t0.OriginalTable
               ORDER BY NewColumn
               FOR XML PATH('')), 1, 2, '') +') ' + 
        'SELECT '+ 
        STUFF((SELECT ', ' + OriginalColumn
               FROM dbo.DataMapping t2
               WHERE t2.NewTable = t0.NewTable
               AND t2.OriginalTable = t0.OriginalTable
               ORDER BY NewColumn
               FOR XML PATH('')), 1, 2, '') +' FROM '+ OriginalTable +'; '
FROM dbo.DataMapping t0  
GROUP BY NewTable, OriginalTable

已更新的rextster链接

第一个版本

这里有一个不需要使用游标的解决方案:

DECLARE @Sql nvarchar(max) = ''

SELECT @Sql += 'INSERT INTO '+ NewTable +'(' + 
        STUFF((SELECT ', ' + NewColumn
               FROM dbo.DataMapping t1
               WHERE t1.NewTable = t0.NewTable
               ORDER BY NewColumn
               FOR XML PATH('')), 1, 2, '') +') ' + 
        'SELECT '+ 
        STUFF((SELECT ', ' + OriginalColumn
               FROM dbo.DataMapping t2
               WHERE t2.NewTable = t0.NewTable
               ORDER BY NewColumn
               FOR XML PATH('')), 1, 2, '') +' FROM '+ OriginalTable +'; '
FROM dbo.DataMapping t0  

EXEC (@Sql)

您可以通过M.Ali提供的示例数据，在rextester上查看实时演示。

- Zohar Peled

---

这个解决方案可以对select和insert into中的错误列进行对齐，这就是为什么我使用游标来确保每次将一行的列附加到一起以获得正确的顺序在select和insert语句中。 - M.Ali

我在尝试使用我的玩具数据时遇到了错误。我将粘贴一个脚本，用于生成我在上面提到的数据集。与此同时，我会回去检查M Ali的版本。 - Reddspark

@M.Ali，它不应该对齐错误的列，因为两个子查询都使用完全相同的order by子句 - 除非有多个映射到同一列，否则每次都应该生成完全相同的顺序。 - Zohar Peled

好的，我已经测试了两种解决方案。两者都能很好地工作，但是我将把分数授予这个简单的解决方案，因为我读到使用游标是不好的（即使它对我来说似乎相当快）。 - Reddspark

Answer 2

样本数据

CREATE TABLE dbo.DataMapping (   OriginalColumn     SYSNAME
                                ,OriginalTable      SYSNAME
                                ,NewColumn          SYSNAME
                                ,NewTable           SYSNAME)

INSERT INTO dbo.DataMapping
(   OriginalColumn
  , OriginalTable
  , NewColumn
  , NewTable)
VALUES 
    (   'Col1' , 'TableA' , 'Col_1' , 'TableAN') ,  (   'Col2' , 'TableA' , 'Col_2' , 'TableAN') 
  , (   'Col3' , 'TableA' , 'Col_3' , 'TableAN') ,  (   'Col1' , 'TableB' , 'Col_1' , 'TableBN') 
  , (   'Col2' , 'TableB' , 'Col_2' , 'TableBN') , (   'Col3' , 'TableB' , 'Col_3' , 'TableBN'); 

查询

Declare   @O_Cols           sysname
        , @N_Cols           sysname
        , @O_Tabl           sysname
        , @N_Tabl           sysname
        , @InsertColsList   NVARCHAR(MAX) =''
        , @SelectColsLIst   NVARCHAR(MAX) =''
        , @Sql              NVARCHAR(MAX);

DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
SELECT OriginalColumn , NewColumn
FROM dbo.DataMapping
WHERE OriginalTable = 'TableA'


OPEN Cur

 FETCH NEXT FROM Cur INTO @O_Cols , @N_Cols

WHILE (@@FETCH_STATUS = 0)
BEGIN
        SET @SelectColsLIst += ISNULL(',' + QUOTENAME(@O_Cols), '');
        SET @InsertColsList += ISNULL(',' + QUOTENAME(@N_Cols), '');

    FETCH NEXT FROM Cur INTO @O_Cols , @N_Cols
END

CLOSE Cur
DEALLOCATE Cur;

    SET @SelectColsLIst = STUFF(@SelectColsLIst, 1 ,1,'');
    SET @InsertColsList = STUFF(@InsertColsList, 1 ,1,'');


SELECT TOP 1  @O_Tabl = OriginalTable ,  @N_Tabl = NewTable 
FROM dbo.DataMapping
WHERE OriginalTable = 'TableA'

 SET @Sql = N' INSERT INTO ' + @N_Tabl + N'( ' + @InsertColsList + N' )'
          + N' SELECT ' + @SelectColsLIst
          + N' FROM ' + @O_Tabl;


PRINT @Sql 
--EXEC sp_executesql @Sql;

请注意：

我做了一个假设，即数据只来自一个表格，并且只到一个表格，源和目标表格的列名可能不同，表格名称也可能不同，但始终是一对一映射。

- M.Ali

---

是的，一旦原始选择完成，返回的结果将是一个表到表的映射。也就是说，我将运行一个单独的选择查询，将相同的源表加载到另一个目标表中；或者将不同的源表加载到同一个目标表中。我现在将尝试实施您的答案，看看是否有效。 - Reddspark

@user1761806，如果您使用此脚本，它将会动态创建插入语句并为您执行。 - M.Ali

谢谢您。我实际上很喜欢这个解决方案，但听说最好尽可能不使用游标。尽管如此，如果有人需要，我将把它粘贴到稍微修改过的代码中，并通过存储过程进行调用。下面是代码： - Reddspark

Answer 3

最终我选择了Zohar的解决方案，但我也很喜欢M Ali的解决方案。这是我如何将M Ali的解决方案修改为存储过程，以便我可以多次调用它来处理不同的源表。

Zohar的解决方案也可以进行微调，只处理特定的表格，但就目前而言，它已经足够满足我的需求了。

CREATE PROCEDURE [dbo].[DataMappingProc]
(
@Tblname as VARCHAR(10)
)
AS 
    BEGIN

    Declare   @O_Cols           sysname
            , @N_Cols           sysname
            , @O_Tabl           sysname
            , @N_Tabl           sysname
            , @InsertColsList   NVARCHAR(MAX) =''
            , @SelectColsLIst   NVARCHAR(MAX) =''
            , @Sql              NVARCHAR(MAX);


    DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT OriginalColumn , NewColumn
    FROM dbo.DataMapping
    WHERE OriginalTable = @Tblname

    OPEN Cur

     FETCH NEXT FROM Cur INTO @O_Cols , @N_Cols

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
            SET @SelectColsLIst += ISNULL(',' + QUOTENAME(@O_Cols), '');
            SET @InsertColsList += ISNULL(',' + QUOTENAME(@N_Cols), '');

        FETCH NEXT FROM Cur INTO @O_Cols , @N_Cols
    END

    CLOSE Cur
    DEALLOCATE Cur;

        SET @SelectColsLIst = STUFF(@SelectColsLIst, 1 ,1,'');
        SET @InsertColsList = STUFF(@InsertColsList, 1 ,1,'');


    SELECT TOP 1  @O_Tabl = OriginalTable ,  @N_Tabl = NewTable 
    FROM dbo.DataMapping
    WHERE OriginalTable = 'TableA'

     SET @Sql = N' INSERT INTO ' + @N_Tabl + N'( ' + @InsertColsList + N' )'
              + N' SELECT ' + @SelectColsLIst
              + N' FROM ' + @O_Tabl;


    EXEC sp_executesql @Sql;
    RETURN 0
    END;
GO

- Reddspark