-1

This code does precisely what I want: finds the difference between two tables, including nulls, and returns them. Thanks to: sql query to return differences between two tables

(
    SELECT * FROM table1
    EXCEPT
    SELECT * FROM table2
)  
UNION ALL
(
    SELECT * FROM table2
    EXCEPT
    SELECT * FROM table1
)

I am having trouble getting this to turn into a temporary table (or even a regular table) to store its results for later use. Is there a way that I can tack on INSERT INTO here or generate a temp table from this beautiful query?

Dale K
  • 25,246
  • 15
  • 42
  • 71
John Stud
  • 1,506
  • 23
  • 46

1 Answers1

3

Select from your existing query as a sub-query INTO the temp table of your choice.

SELECT *
INTO #temp1
FROM (
    (
        SELECT * FROM @table1
        EXCEPT
        SELECT * FROM @table2
    )  
    UNION ALL
    (
        SELECT * FROM @table2
        EXCEPT
        SELECT * FROM @table1
    )
) X
Dale K
  • 25,246
  • 15
  • 42
  • 71
  • This is close, but perhaps the UNION ALL not being separated is leading to zero rows being inserted. Its not quite a complete capture of the select / except query. EDIT: After playing with the parens, this works but the UNION ALL cannot be in the middle of the two. It needs separation! – John Stud Oct 27 '21 at 20:49
  • 1
    @JohnStud no idea what you mean, this syntax inserts the results of your existing query into the temp table. If the results aren't quite what you want then thats due to your existing query. As always a [mre] with sample data and desired results is ideally required in order to provide the desired solution. – Dale K Oct 27 '21 at 20:50
  • 2
    Without parenthesis it has a different meaning, due to order of operations https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f8cb63442bacbc99fe3f1605ddf7d42e – Charlieface Oct 27 '21 at 21:39