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
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
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.