40

If we want to create a new table from an existing one in SQL Server we can do the following

SELECT * into Table2
from Table1
WHERE 1=2

Whats the point of where clause? I tried it without the where clause and it ran fine. I've seen this where clause in many examples on internet but not the reason why it is required.

Thomas Stringer
  • 42,224
  • 9
  • 117
  • 154
Muhammad Hasan Khan
  • 683
  • 2
  • 6
  • 11

2 Answers2

55

The reason you put the WHERE 1=2 clause in that SELECT INTO query is to create a field-copy of the existing table with no data.

If you did this:

select *
into Table2
from Table1

Table2 would be an exact duplicate of Table1, including the data rows. But if you don't want the data contained in Table1, and you just want the table structure, you put a WHERE clause to filter out all of the data.

BOL SELECT INTO Reference quote:

SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.

If your WHERE clause has no resulting rows, none will be inserted into the new table: Therefore, you end up with the duplicate schema of the original table with no data (which would be the desired result in this case).

The same effect can be achieved with TOP (0), for example:

select top (0) *
into Table2
from Table1;

Note: The SELECT INTO will not duplicate the source table's indexes, constraints, triggers, or partition schema.

Andriy M
  • 22,983
  • 6
  • 59
  • 103
Thomas Stringer
  • 42,224
  • 9
  • 117
  • 154
-2

Maybe someone can fine tune my answer here, but in general, when you select...into #tmptbl you are locking up the tempdb. If your query is expensive (i.e. takes a long time) this can have negative effects on other processes in the database. To alleviate this, setting your filter as where 1=2 will make the table instantly, and then you can do an INSERT INTO #tmptbl with your actual filter. That way the tempdb, which is needed for other processes, can be freed up during your long-running query.

One downside is that in this quick table create, it will only look at the top few rows to determine the data type for each field, so in other words if the first few values of a varchar field are less than 20 characters, it will be set as varchar(20), regardless of whether there is a longer value in the full query, which will then give you a string value would be truncated error during your insert

Sam Cohen-Devries
  • 97
  • 1