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
我有一个DataMapping表
"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.
然后使用它返回的信息动态创建并执行基于OriginalColumn到NewColumn映射的Insert to NewTable。
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
3 个解决方案
#1
2
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
更新了rextster链接
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.
借助M.Ali的样本数据,您可以在rextester上看到现场演示。
#2
0
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.
我假设数据只来自一个表并且只到一个表,列名在源表和目标表中可以不同,表名可以不同但总是一对一映射。
#3
0
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的解决方案;但我确实很喜欢M Ali的解决方案。以下是我将M Ali的解决方案修改为存储过程的方法,以便我可以针对不同的源表多次调用它。
Zohar's solution can be tweaked to only process specific tables too but for my purposes for now, it was fine as it is.
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
#1
2
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
更新了rextster链接
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.
借助M.Ali的样本数据,您可以在rextester上看到现场演示。
#2
0
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.
我假设数据只来自一个表并且只到一个表,列名在源表和目标表中可以不同,表名可以不同但总是一对一映射。
#3
0
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的解决方案;但我确实很喜欢M Ali的解决方案。以下是我将M Ali的解决方案修改为存储过程的方法,以便我可以针对不同的源表多次调用它。
Zohar's solution can be tweaked to only process specific tables too but for my purposes for now, it was fine as it is.
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