Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 41 down vote accepted

The reason you put the WHERE clause in that SELECT INTO query is to just 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).

Note: The SELECT INTO will not duplicate the source table's indexes.

share|improve this answer
    
Does it create indices as well or just the take structure? –  Aaron Brown Apr 15 '12 at 0:48
2  
No, there is no such syntax (yet). –  Aaron Bertrand Apr 15 '12 at 2:50
1  
Replica? No data, no constraints, no indexes, ... that's like buying a replica of a painting and discovering it is merely a blank canvas of the same dimensions of the original ;) –  onedaywhen Apr 16 '12 at 8:10
12  
I normally just do a SELECT TOP 0 *... for this which I think makes the intention more obvious –  JNK Apr 18 '12 at 13:47
1  
@JNK Yeah, I'll tend to agree with you there. Conditional statements that will never evaluate to true always look odd to me. –  Thomas Stringer Apr 18 '12 at 13:50
show 7 more comments

If we use where clause it will copy only schema of the table.Without where clause it will copy the schema as well as data.

share|improve this answer
3  
I see this is your first post on the site, welcome. Your answer is not wrong but the question has been already answered, more than a year ago. I suggest you keep your answer (and not delete it), only if it provides some additional information that the other existing answer does not. –  ypercube Sep 2 '13 at 9:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.