siggemannen (1/28/2013)
Can SQL Server guarantee that if I issue a command like:
INSERT INTO T(...)
SELECT t.a, t.b, 1
FROM sometable t
UNION
SELECT -t.a, t.b, 1
FROM sometable t
, both "sides" of union will retrieve same table rows?
Not sure what you mean by "same table rows", but as one other poster pointed out, UNION will return only one instance of a row that is returned by more than one of the UNIONed queries with identical values in each column. That won't ever happen in your example, assuming that "-t.a" actually returns the negative of the value in t.a. If you want all rows returned by the UNIONed queries, even if they return "duplicate" rows, use UNION ALL.
Also, I don't know if this pertains to your question, but each query in a UNION/UNION ALL can have its own WHERE clause to set the conditions for the rows to be returned by that query.
Jason Wolfkill