Хранимая процедура для динамического отображения данных

Я знаю, что это должно быть уже решено, но я с трудом пытаюсь найти решение. Я пытался найти: таблица отображения вставки динамического отображения хранимых процедур в Google и здесь.

У меня есть таблица DataMapping, которая говорит

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

Как следует из названий столбцов, эта таблица будет содержать метаданные о том, как данные из одной таблицы должны быть загружены в другую существующую таблицу.

Я хочу написать хранимую процедуру, которая выдаст

select *  
from DataMapping 
where OriginalTable = XXXX

а затем использовать эту информацию, которую он получает обратно, чтобы динамически создавать и выполнять Insert into NewTable на основе OriginalColumn в NewColumn отображение.

Вот код, который будет генерировать пример проблемы:

/****** 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
Источник

3 ответа

Решение

Обновление: при написании моего исходного ответа я ошибочно предполагал, что между каждой парой таблиц и столбцов будет уникальное сопоставление (если бы исходный вопрос включал в себя образцы данных, которые он включает в настоящее время, этого предположения можно было бы избежать), и поэтому мой ответ был неверным.

Теперь, когда вопрос обновлен и теперь содержит правильные примеры данных, я могу обновить свой ответ, добавив еще одно условие к подзапросам и группе к исходному запросу, который мне удалось получить работающее решение:

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)

Вы можете увидеть живое демо на rextester благодаря образцу данных М.Али.

Источник

Я решил пойти с решением Zohar в конце; однако мне очень понравилось решение М Али. Вот как я изменил решение 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
Источник

Пример данных

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;

Заметка

Я сделал предположение, что данные поступают только из одной таблицы и идут только в одну таблицу, имена столбцов могут различаться в исходной и целевой таблицах, а имена таблиц могут различаться, но это всегда сопоставление один к одному.

Источник
Другие вопросы по тегам