Create Table in Sql using Select Statement

In SQL Server, one can create a table using SELECT statement. Let's learn how we do it.

Let's take a scenario where you are ordering your food in restaurant. But you are not asking what you need rather you are asking give me exactly the same food as you gave it to the person in table no 5.

SELECT * INTO NewTable FROM OldTable

This is the basic query we are going to try in this tutorial. Let's do it step by step.

We already have a table named tblUser as shown below:

Let's create a new table with exactly same columns and data type using the above query.

This statement makes a replica of old table and copies data (from old table) to the new table.

Here, * tells server to copy all the columns whereas we can mention name of only those columns that are required.

SELECT Column1, Column2, column3 INTO newTable FROM OldTable

Copy only table structure but not data

we can use some false condition after where clause. e.g.

SELECT * INTO newTable FROM OldTable WHERE 1= 2

Copy table structure and some data that satisfies our conditions

SELECT * INTO newTable FROM OldTable WHERE some_conditions

e.g.

SELECT * INTO newTable FROM OldTable WHERE ID > 10

Create Table

 In SQL server, table can be created by two ways:
- using graphical interface

- using query

Using GUI

- Go to the Database, expand it, you will see folder named Tables.

- Right Click on the Tables and then click Table

- Now provide Name of columns and its data type and then save it.

Using query 

Here, we are trying to make column Id as IDENTITY (we will discuss it later).  IDENTITY (1,1), is Seed and Increment, written which means it will start from 1 and incremented by 1. i.e. we don't have to insert any value in that column. it will automatically start from 1 and then increases as 2, 3, 4,.....

NULL means it allows null value (okay if no value if provided) while NOT NULL means we have to provide some value.

After writing this query we can run it (or press F5). then a message will be seen as shown below:

This means our code executed.

Now table is created inside Tables Folder. Right click on the folder and click Refresh. You should be able to see it there.