In SQL Server, INTERSECT only works with single rows. If you want it to distinguish between repeated lines, you will need to make the lines different. The only way I can think of this is to add another column and fill it with unique values for each duplicate, but in such a way that the resulting rows are comparable in different tables.
The problem, however, is that there is still no universal syntax for this. For example, you can use ROW_NUMBER () to list each duplicate, but you will have to write its PARTITION BY clause for each case separately: there is no PARTITION BY * , not SQL Server, at least.
In any case, for the purpose of illustration, here is how the ROW_NUMBER method looks:
SELECT A, B, C, D, E, ROW_NUMBER() OVER (PARTITION BY A, B, C, D, E ORDER BY (SELECT 1)) FROM dbo.A INTERSECT SELECT A, B, C, D, E, ROW_NUMBER() OVER (PARTITION BY A, B, C, D, E ORDER BY (SELECT 1)) FROM dbo.B ;
As written above, the query will also return an extra column, a row number column, in the output. If you want to suppress it, you need to make the request more complex:
SELECT A, B, C, D, E FROM ( SELECT A, B, C, D, E, rn = ROW_NUMBER() OVER (PARTITION BY A, B, C, D, E ORDER BY (SELECT 1)) FROM dbo.A INTERSECT SELECT A, B, C, D, E, rn = ROW_NUMBER() OVER (PARTITION BY A, B, C, D, E ORDER BY (SELECT 1)) FROM dbo.B ) AS s ;
And just for clarification, when I said above, there was no universal syntax, I meant that you could not do this without resorting to dynamic SQL. With dynamic SQL, a lot of things are possible, but such a solution will be much more complex and, in my opinion, much less convenient.
Again, to illustrate this point, this is an example of how you could solve it using dynamic SQL:
DECLARE @table1 sysname, @table2 sysname, @columns nvarchar(max), @sql nvarchar(max) ; SET @table1 = 'dbo.A'; SET @table2 = 'dbo.B'; -- collecting the columns from one table only, -- assuming the structures of both tables are identical -- if the structures differ, declare and populate -- @columns1 and @columns2 separately SET @columns = STUFF( ( SELECT N', ' + QUOTENAME(name) FROM sys.columns WHERE object_id = OBJECT_ID(@table1) FOR XML PATH (''), TYPE ).value('text()[1]', 'nvarchar(max)'), 1, 2, '' ); SET @sql = N'SELECT ' + @columns + N' FROM ( SELECT ' + @columns + N', ROW_NUMBER() OVER (PARTITION BY ' + @columns + N' ORDER BY (SELECT 1)) FROM ' + @table1 + N' INTERSECT SELECT ' + @columns + N', ROW_NUMBER() OVER (PARTITION BY ' + @columns + N' ORDER BY (SELECT 1)) FROM ' + @table2 + N' ) AS s '; EXECUTE sp_executesql @sql;
You can probably see now what I meant by “much more complex,” at least.