sql - 动态数据映射的存储过程
问题描述
我知道这一定已经解决了,但我很难找到解决方案。我试过在 Google 和这里搜索:存储过程动态数据映射插入映射表。
我有一个 DataMapping 表,上面写着
"OriginalColumn","OriginalTable","NewColumn","NewTable"
正如列名所暗示的,该表将包含有关如何将一个表中的数据加载到另一个现有表中的元数据。
我想编写一个存储过程,它将发出
select *
from DataMapping
where OriginalTable = XXXX
然后使用它返回的信息动态创建和执行Insert into NewTable
基于OriginalColumn
toNewColumn
映射。
这是将生成示例问题的代码:
/****** 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
最终解决方案
这是最终解决方案,我在下面采用了最佳答案并将其转换为存储过程,在您不想重新运行整个导入过程的情况下,我们可以选择填充哪个目标表。
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
运行存储过程
DECLARE @return_value int
EXEC @return_value = [dbo].[DataMappingProc2]
@DestinationTable = N'DestinationTable'
SELECT 'Return Value' = @return_value
GO
解决方案
更新:在写我的原始答案时,我错误地假设每对表和列之间会有一个唯一的映射(如果原始问题包括它现在包含的示例数据,那么假设将被避免) - 因此我的答案是错误的。
现在问题已更新以包含正确的示例数据,我可以更新我的答案 - 通过向子查询添加另一个条件并将 group by 添加到原始查询中,我设法获得了一个有效的解决方案:
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
第一个版本
这是一种不需要使用游标的解决方案:
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)
由于 M.Ali 的示例数据, 您可以在 rextester 上看到现场演示。
推荐阅读
- python - 将列添加到具有不同行数的数据框
- r - ggplot geom_boxplot 颜色和组变量
- opencv - vcap 无法打开 [udp @ 0x56378e8a76a0] 绑定失败:权限被拒绝
- rust - 如何将 main 中初始化的变量传递给 Rocket 路由处理程序?
- c# - 如何仅通过更改 1 或 2 行代码来修复以下 C# 代码中的错误?
- go - 如何自定义 go-present 模板?
- r - R 中的 Lattice 程序可能会导致加载其他包和库时出错
- latex - 如何使用 maketitle 使标题看起来像这样
- django - 如何在 Django 中为页面添加书签?
- javascript - 在 JavaScript 中对字符串进行编码和解码