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.

A network-related or instance-specefic error occured in sql server

 While trying to connect to SQL Server many of us must have faced an error "A network-related or instance-specific error occurred". So, we will be learning how to solve this problem in this article.

This issue is seen when SQL server running in background (service) is stopped.

See red circle, there is nothing in status. It means the service is stopped.

In above figure, see red mark at left side, you can see start the service. Just click that start and the service starts running and the problem will be solved.

Alternatively, you can right click on highlighted line (SQL Server) and then click start.

Depending on the version you installed, displayed option might be different.

How to create database in SQL server?

 In this article, we will discuss two ways of creating database in SQL Server.

1) from GUI (graphical user interface)

2) from query 

1) GUI method

First of all, right click on databases as shown in figure and then option pops up. Click on "New Database" as shown below.

A new window is seen now as shown below:

Provide Name of database and then click OK. 

Now, your new database is created.

2) Using query

Click in "New Query". A new query analyzer will be displayed.

Type the following query in query analyzer

CREATE DATABASE YourDatabaseName;

Click Execute (or press F5) to run the query. Database will be created.