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

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.