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

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?

除了SQL,我不希望我在OUTPUT子句中使用DISTINCT。我想到的解决方法是为输出创建临时表,然后将INSERT DISTINCT插入到IdListTable中。关于不同解决方法的任何想法?

1 个解决方案

#1

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.

将IdListTable替换为Output语句中的临时表(或表变量,具体取决于行数)。然后使用Select Distinct从临时表中向IdListTable运行第二个Insert语句。

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