开发者

Can I use DISTINCT in the OUTPUT clause?

开发者 https://www.devze.com 2023-03-06 08:37 出处:网络
I\'m trying something like: INSERT INTO MyTable ( Col1 ,Col2 ) OUTPUT DISTINCT -- issue is with DISTINCT INSERTED.Col1

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?


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

精彩评论

暂无评论...
验证码 换一张
取 消