PHYData DBA (2/23/2015)
bantrim (2/22/2015)
Eric M Russell (2/22/2015)
On occasion I've used: SELECT.. WHERE 1 = 0. For example, I may want to select into a temp table that is derived from another table but initially empty. Also, just for the sake of the application, there may be a need to have a stored procedure return an empty resultset with the usual column layout, even when invalid input parameters have been specified or an error has occured.Same here. It's especially useful in using BULK INSERT, where you need the target table created beforehand. For certain ETLs, I bulk insert into a temp table, then merge the deltas into the target table.
There are programing structures that handle all of this.
None of them add a useless check to a where statement that at best is not even executed.
The statement is a SELECT..INTO that definitely will be executed, to create the empty temporary table.
I find this is the quickest way to create a temporary clone of a permanent table, especially if it contains columns defined as UDDTs.
If you explicitly CREATE the #temp table, you are forced to declare such columns using the underlying native data type for the UDDT, defeating the purpose of the abstraction.