我可以在 OUTPUT 子句中使用 DISTINCT 吗？

发布于 2024-11-08 00:46:19 字数 463 浏览 0 评论 0原文

我正在尝试类似的操作：

INSERT INTO MyTable (
       Col1
      ,Col2 )
OUTPUT DISTINCT -- issue is with DISTINCT
       INSERTED.Col1
      ,@otherParameter
       INTO IdListTable
SELECT ColA
      ,ColB
      ,SUM(ImportantNumber)
FROM MyOtherTable
GROUP BY ColA, ColB

除了 SQL 不希望我在 OUTPUT 子句中使用 DISTINCT 。我想到的解决方法是为输出创建一个临时表，然后将 INSERT DISTINCT 插入到 IdListTable 中。关于不同的解决方法有什么想法吗？

原文

I'm trying something like:

INSERT INTO MyTable (
       Col1
      ,Col2 )
OUTPUT DISTINCT -- issue is with DISTINCT
       INSERTED.Col1
      ,@otherParameter
       INTO IdListTable
SELECT ColA
      ,ColB
      ,SUM(ImportantNumber)
FROM MyOtherTable
GROUP BY ColA, ColB

Except SQL doesn't want me to use DISTINCT in the OUTPUT clause. The workaround I thought of was to create a temp table for the output, then INSERT DISTINCT into the IdListTable. Any ideas on a different workaround?

分享到QQ

分享到微博

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

发布评论

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

絕版丫頭 2024-11-15 00:46:19

在 Output 语句中将 IdListTable 替换为临时表（或表变量，具体取决于行数）。然后使用 Select Distinct 将第二个 Insert 语句从临时表运行到 IdListTable 中。

INSERT INTO MyTable (
       Col1,
       Col2 )
OUTPUT 
       INSERTED.Col1,
       @otherParameter
       INTO #tempIdListTable
SELECT ColA,
       ColB,
       SUM(ImportantNumber)
FROM MyOtherTable
GROUP BY ColA, ColB

Insert into IdListTable
Select distinct col1, col2 from #tempIdListTable

Replace IdListTable with a temporary table (or a table variable depending upon the number of rows) in the Output statement. Then run a second Insert statement into IdListTable from the temporary table with a Select Distinct.

INSERT INTO MyTable (
       Col1,
       Col2 )
OUTPUT 
       INSERTED.Col1,
       @otherParameter
       INTO #tempIdListTable
SELECT ColA,
       ColB,
       SUM(ImportantNumber)
FROM MyOtherTable
GROUP BY ColA, ColB

Insert into IdListTable
Select distinct col1, col2 from #tempIdListTable

回复收藏 0 原文

~没有更多了~