SQL Interview Question: How to create another table using existing table?

Here i am discussing about, "How to create another table using existing table?" or "How to create Duplicate table in SQL Server?". There is a simple SQL Query to create duplicate table. I have tried and tested. Hope this work for you-

Scenario 1 (Wrong Answer)

My question was, I want to create a table based on the definition of another table. After searching on Google I found the below result over many website:

  CREATE TABLE new_Table_Name
  AS
  (SELECT * FROM old_Table_Name);

In SQL Server, I found below error:

I have removed "( )", and again i have tried. Again I got same error like below:

Note : There is no such syntax in SQL Server, though CREATE TABLE AS ... SELECT does exist in SQL Server PDW(Parallel Data Warehouse).

Scenario 2 (Correct Answer)

In SQL Server you can use below query:

To create an empty table:

SELECT * INTO new_Table_Name FROM old_Table_Name WHERE 1=0;

To create a copy of table including all data:

then remove the WHERE clause like below query:

SELECT * INTO new_Table_Name FROM old_Table_Name;

Note: Above statements creates the same column structure (including an IDENTITY column if one exists). It does not copy any indexes, constraints, triggers etc.

Summary

Knowing these simple techniques can make your code easier to understand & simpler to read. Hope this will helpful for you. If there any confusion. Please comment. I will help you.
Above queries tried and tested by me.