SQL Tip: Replicating Structure of a Table to a new table (Where 1=2)

As SQL Developers we will have a requirement like we might need to create a temporary table, which would replicate the exact structure of a table, through SQL Queries.

For example

We have a table Customers, you want to create a new table(Customers1) temporarily for testing purpose or something.

You might directly think it’s pretty easy by Taking Create table script for [Customers] table and rename the Table name in create table query to “[Customers1]” and execute. Table is created what’s the big deal about it.

One job we can do in “n” number of ways. Finding most number of ways the same job can be done and choosing the best one among is a professional developers job.

Well other than using a “create query“, we can do it with a single line of query.

[code lang=”sql”]
select * into Customers1 from Customers Where 1 =2;
[/code]

The above query replicates the structure of the Customers table and creates a new table Customers1. Since we are using “select * into”, you might think that it will copy the data also from Customers table to Customers1.

The fact is it will not, we are doing a little trick by specifying a WHERE clause. So WHERE 1=2 will never be true, because 1 is not equal to 2. so there will be no records returned by the query “select * from Customers Where 1 =2“. Thus the structure only copied and a new table is created.

If there is such a simple query is there, why we need to go for a CREATE TABLE queries. Cool Na. I hope this was informative, most of you have already aware of it.