SQL查询以将动态列标题与另一个静态表列进行比较

发布于 2025-01-28 19:29:47 字数 623 浏览 2 评论 0原文

我目前使用SSIS动态导入文件中的文件中的SQL表，该过程的一部分将列标题从源文件导入表的第一行（下表1）。

然后，我想将表1的标题与具有静态列的表2进行比较，最重要的是，表2中不存在的表1中的

。

列	任何
标题	自定义名称

和表2具有以下静态列标题：

customerId	customername
1	joe
2	daniel，

因此基本上是我加载文件和新的列标头（在此示例中，添加了customerLocation）并将其加载到表1中，我想要SQL查询以比较2个表，并强调当前表2中缺少/不存在列的客户关闭。

我正在考虑使用sys表进行比较的行。

原文

I currently import a file dynamically using SSIS into a SQL table, part of the process imports the column headers from the source file into the first row of a table (table 1 below).

I then want to compare the headers from table 1 with table 2 which has static columns, and most importantly highlight any column headers from table 1 that do not exist in table 2.

So for example Table 1 looks like this:

Column 1	Column 2
CustomerID	CustomerName

And table 2 has static column headers like the following:

CustomerID	CustomerName
1	Joe
2	Daniel

So basically when I load a file and a new column header is added (in this example lets say CustomerLocation is added) and loaded into table 1, I want a SQL query to compare the 2 tables and highlight that the column CustomerLocation is missing/does not exist in table 2 currently.

I was thinking along the lines of using the sys tables to compare.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清音悠歌 2025-02-04 19:29:47

当然，您可以使用sys.columns将静态列从表2返回，并将它们与表1中的动态列进行比较，并在选择第一行的选择中使用unvivot。

我发现，将所有这些包装在T-SQL块中并插入两个列表中的临时表要容易得多，然后将数据类型冲突引起的比较（可能是通过使用铸件解决）

BEGIN
  DECLARE @table1 TABLE (colname VARCHAR(MAX))
  DECLARE @table2 TABLE (colname VARCHAR(MAX))

  INSERT INTO @table1 SELECT COLNAME FROM (SELECT a, b, c FROM TABLE1 WHERE...first row condition) a UNPIVOT (COLNAME FOR COLS IN ([a],[b],[c])) a

  INSERT INTO @table2 SELECT CAST (name  AS NVARCHAR(100)) name FROM sys.columns WHERE object_id = OBJECT_ID('TABLE2')


  SELECT a.colname cols1, b.colname cols2 
    FROM @table2 a
    FULL OUTER JOIN @table1 b ON (a.colname = b.colname)
END

您可以轻松更改最终选择以返回的最终选择。你想要什么

You certainly could use sys.columns to return your static columns from Table2 and compare them to the dynamic columns in Table1 and use UNPIVOT on a select of your first row.

I have found that it was far easier to wrap this all in a T-SQL block and insert to two lists into temp tables before comparing due to data type conflicts (probably be solved by using CAST)

BEGIN
  DECLARE @table1 TABLE (colname VARCHAR(MAX))
  DECLARE @table2 TABLE (colname VARCHAR(MAX))

  INSERT INTO @table1 SELECT COLNAME FROM (SELECT a, b, c FROM TABLE1 WHERE...first row condition) a UNPIVOT (COLNAME FOR COLS IN ([a],[b],[c])) a

  INSERT INTO @table2 SELECT CAST (name  AS NVARCHAR(100)) name FROM sys.columns WHERE object_id = OBJECT_ID('TABLE2')


  SELECT a.colname cols1, b.colname cols2 
    FROM @table2 a
    FULL OUTER JOIN @table1 b ON (a.colname = b.colname)
END

You can easily change the final select to return what you want

回复收藏 0 原文

~没有更多了~