时间戳

首页 > 解决方案 > 在不知道表中内容的情况下映射列

sql-server - 在不知道表中内容的情况下映射列

问题描述

我有一个奇怪的情况，我将有来自各种来源的数据（所有平面文件，没有一个在我的控制范围内，无论我要求标准格式多少次，我都会得到不同的列标题和不同的列顺序） . 我们没有人力手动检查这些文件以确定哪些列是重要的。每个平面文件将有两到六个“标识”列。但是，某些列单独地不是唯一的，但它们的组合可以形成唯一的键。总而言之，每个平面文件可以有大约一百列。

因此，最初，我计划将数据加载到临时表中，并要求用户识别哪些列包含哪些数据。一旦我知道这一点，我就可以毫无问题地处理文件。我将有两到六列来识别与现有记录的匹配以及我应该收集的其他数据（全部由用户识别）。

然后我被要求添加系统“推荐”哪些数据列是哪些的能力。为此，我的计划很重要。我会计算每列有多少非空值，然后计算这些非空值中有多少与六个可能的列中的每一个匹配。从那里，我可以采用一个简单的比率来确定包含的数据属于该特定类型的可能性。会有一些高估的列不是唯一的，但总的来说，它工作得很好。问题是它非常慢。

我创建了一个名为 UploadedTableColumn 的元数据表，其中包含源文件的每个列标题以及它在数据库中映射到的列。这是我更新计数的存储过程：

CREATE PROCEDURE stored_Procedure 
    @FileLoadID INT
AS
BEGIN
    DECLARE @SqlCommand NVARCHAR(MAX)

    DECLARE the_cursor CURSOR FAST_FORWARD FOR 
    SELECT N'UPDATE UploadedTableColumn SET NumberNonemptyRows = (SELECT COUNT(*) FROM ' + DestinationTableName + N' WHERE ISNULL(' + DestinationColumnName + N','''') <> ''''),' + CHAR(13)
        + N'NumberID1Rows = (SELECT COUNT(*) FROM ' + DestinationTableName + N' WHERE ISNULL(' + DestinationColumnName + N','''') IN (SELECT ID1 FROM ID1Table) AND ISNULL(' + DestinationColumnName + N','''') <> ''''),' + CHAR(13)
        + N'NumberID2Rows = (SELECT COUNT(*) FROM ' + DestinationTableName + N' WHERE ISNULL(' + DestinationColumnName + N','''') IN (SELECT ID2 FROM ID2Table) AND ISNULL(' + DestinationColumnName + N','''') <> ''''),' + CHAR(13)
        + N'NumberIDDateRows = (SELECT COUNT(*) FROM ' + DestinationTableName + N' WHERE IIF(ISDATE(' + DestinationColumnName + N')=1,IIF(CAST(' + DestinationColumnName + N' AS DATE) IN (SELECT IDDate FROM IDDateTable),1,0),0) = 1 AND ISNULL(' + DestinationColumnName + N','''') <> ''''),' + CHAR(13)
        + N'NumberID4Rows = (SELECT COUNT(*) FROM ' + DestinationTableName + N' WHERE ISNULL(' + DestinationColumnName + N', '''') IN (SELECT ID4 FROM ID4Table) AND ISNULL(' + DestinationColumnName + N','''') <> ''''),' + CHAR(13)
        + N'NumberID5Rows = (SELECT COUNT(*) FROM ' + DestinationTableName + N' WHERE ISNULL(' + DestinationColumnName + N', '''') IN (SELECT ID5 FROM ID5Table) AND ISNULL(' + DestinationColumnName + N','''') <> ''''),' + CHAR(13)
        + N'NumberID6Rows = (SELECT COUNT(*) FROM ' + DestinationTableName + N' WHERE ISNULL(' + DestinationColumnName + N', '''') IN (SELECT ID6 FROM ID6Table) AND ISNULL(' + DestinationColumnName + N','''') <> '''')' + CHAR(13)
        + N'WHERE DestinationTableName = ''' + DestinationTableName + N''' AND DestinationColumnName = ''' + DestinationColumnName + N''' AND FileLoadID = ' + CAST(@FileLoadID AS NVARCHAR) + N';' + CHAR(13) As SqlCommand
    FROM UploadedTableColumn
    WHERE FileLoadID = @FileLoadID

    OPEN the_cursor

    FETCH NEXT FROM the_cursor
    INTO @SqlCommand

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXECUTE(@SqlCommand)

        FETCH NEXT FROM the_cursor 
        INTO @SqlCommand
    END

    CLOSE the_cursor
    DEALLOCATE the_cursor
END

有更快的方法吗？

标签： sql-servertsqlquery-performance

解决方案

一个可能有帮助的小改变。

您说您将源表的每一列都保存在 UploadedTableColumn 中 - 您不需要这样做，您的光标正在遍历许多不必要的列。您可以通过先发制人的列名匹配消除很多问题。

因此，从您的 ID1Table、ID2Table 等中获取所有可能的 ID 列的组合列表，并且只将与 DestinationTableName 中的列实际匹配的列拉入 UploadedTableColumn。

基于您的源数据中可能不超过 6 列具有匹配的 ID 列名称，您现在只检查那些而不是全部 100+。

当然，如果您让人们发送没有标头且没有约定格式的数据，这对您没有帮助。

获取所需列的伪代码：

SELECT name
FROM sys.columns
WHERE [object_id] = OBJECT_ID('DestinationTableName')
AND Name IN
(
SELECT ID1 AS IDColumn FROM ID1Table
UNION ALL
SELECT ID2 AS IDColumn FROM ID2Table
...
)

推荐阅读