title: Stored Procedure for dynamic data mapping
body: <p>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.</p>
  
  <p>I have a DataMapping table that says </p>
  
  <pre><code>"OriginalColumn","OriginalTable","NewColumn","NewTable"
  </code></pre>
  
  <p>As the column names suggest this table will contain meta data of how data in one table should be loaded into another existing table. </p>
  
  <p>I want to write a stored procedure that will issue a</p>
  
  <pre><code>select *  
  from DataMapping 
  where OriginalTable = XXXX 
  </code></pre>
  
  <p>and then use that information it gets back to dynamically create and execute an <code>Insert into NewTable</code> based on the <code>OriginalColumn</code> to <code>NewColumn</code> mapping.</p>
  
  <hr>
  
  <p>Here is code that will generate the sample problem:</p>
  
  <pre><code>/****** 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
  </code></pre>
  
  <p><strong>Final Solution</strong></p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Running the Stored Procedure</strong></p>
  
  <pre><code>DECLARE @return_value int
  EXEC    @return_value = [dbo].[DataMappingProc2]
          @DestinationTable = N'DestinationTable'
  
  SELECT  'Return Value' = @return_value
  GO
  </code></pre>