Can I use DISTINCT in the OUTPUT clause?
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
精彩评论