Stored Procedure for dynamic data mapping

Question

I know this must have been solved already but I'm having a hard time trying to find the solution. I've tried searching for: stored procedure dynamic data mapping insert mapping table on Google and on here.

I have a DataMapping table that says

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

As the column names suggest this table will contain meta data of how data in one table should be loaded into another existing table.

I want to write a stored procedure that will issue a

select *  
from DataMapping 
where OriginalTable = XXXX 

and then use that information it gets back to dynamically create and execute an Insert into NewTable based on the OriginalColumn to NewColumn mapping.

---

Here is code that will generate the sample problem:

/****** 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

Final Solution

Here is the final solution in which I've taken the best answer below and turned it into a Stored Procedure that allows we me to choose which Destination table I populate, in cases where you do not want to re-run the entire import process.

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

Running the Stored Procedure

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

SELECT  'Return Value' = @return_value
GO

Harpole

Procedural code is highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. – Deaver

Done. Added sql-server – Harpole

How about using the mapping table to generate a set of views ahead of time, and then just using those? – Synaesthesia

can you provide sample data ? and dynamic output of the query to be constructed. – Ruthy

Now that the question contains proper sample data, I've updated my answer. I think you should find it a proper solution to your problem. – Outclass

Answer 1

Update: When writing my original answer I wrongly assumed there will be a unique mapping between each pair of tables and columns (Had the original question included the sample data it includes now that assumption would be avoided) - and therefor my answer was wrong.

Now that the question is updated to include proper sample data, I can update my answer - by adding another condition to the sub queries and a group by to the original query I've managed to get a working solution:

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

Updated rextster link

First version

Here is one solution that does not require the use of cursor:

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)

You can see a live demo on rextester thanks to M.Ali's sample data.

Outclass

This solution can align wrong columns in select and insert into, this is why I used cursor to make sure columns from one row gets appended to at a time to get the correct order in select and insert statements. – Dragline

Yep I'm getting an error so far on this one when I try it on my toy data. Will paste in a script that will generate the dataset I'm working with in my question above. Will go back to checking M Ali's version in the mean time. – Harpole

@Dragline It shouldn't align wrong columns since both sub queries are using the exact same order by clause - and unless there are multiple mappings to the same column it should generate the exact same order each time. – Outclass

Ok I have tested both solutions. Both work well but I shall award the points to this simply as I have read that the use of cursors is bad (even though it seemed pretty fast to me). – Harpole

Answer 2

Sample data

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'); 

Query

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;

Note

I have made an assumption that the data is coming from only one table and going to only one table, column names can differ in the source and destination tables and the table names can be different but it is always one to one mapping.

Dragline

Yep once the original select is done, the returning result would be a one table to one table mapping. I.e. I would then run a separate select query to load either the same source table into another destination table; or to load a different source table into the same destination table. I will try implementing your answer now to see if it works. – Harpole

@user1761806 then this script should work, it will create the insert statements on the fly and execute them for you. – Dragline

Thanks for this. I actually quite liked this solution but I hear it's better not to use Cursors if possible, Nevertheless I'll paste into a slightly modified version of your code in which I call it through a Stored Proc, below in case anyone wants it. – Harpole

Answer 3

I chose to go with Zohar's solution in the end; however I did quite like M Ali's solution. Here is how I modified M Ali's solution into a Stored Procedure so that I could call it multiple times for different source tables.

Zohar's solution can be tweaked to only process specific tables too but for my purposes for now, it was fine as it is.

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

Harpole