How to Create a Table in SQL Server Management Studio

In this SQL Server tutorial, I will show you how to create a table in SQL Server Management Studio. As a database developer, you must know how to create a table to store the information in it. Tables are important objects in SQL. Server databases and their design are a base for both DBAs and database developers. Let’s get started to learn more.

How to Create a Table in SQL Server Management Studio

Let us discuss all the possible approaches individually.

Approach-1: Using SQL Server Management Studio (SSMS) GUI

To create a table in SQL Server Management Studio, follow the below steps.

1. The first and foremost step is to start SQL Server Management Studio (SSMS) and connect with the Database Engine. To do this, enter the database access credentials and click on Connect to connect to the database instance.

2. Make sure you have already created a database. Now, near your database, click the plus sign and expand the database directory.

3. Now, we will create a simple employee table with Column Name, Data Type, and Allow Nulls. We have taken the employee details as ID, First Name, Last Name, Age, Role, and Salary.

4. After adding the Column Name and Data Type, go to the menu bar and click the Save option or click Ctrl + S to save the table in the database.

5. A new window will pop up. In that, we can specify the table name and then click OK to save the table. [Here, I have taken the table name as Employee Details].

6. Now expand the Tables directory in Object Explorer. The newly created Employee Details table will be listed in the Tables directory.

Now, let us insert the data into the table we have created.

Insert Data into Table in Microsoft SQL Server Management Studio

We have created a table. So now we will add values for that table in server Management studio. Similar to the creation of an SQL Server database table, we can use either a Graphical User Interface (GUI) or SQL Query to insert data into a table.

Using Graphical User Interface Options(GUI)

Follow the below steps to add values to the table using GUI Options.

1. After creating a table in SQL Server Management Studio, right-click and click on Edit Top 200 Rows. This will open the table file in row-column format.

2. Now, we can add values or data into the table just like in a spreadsheet (row-column manner).

3. Here, a value has been added. If you want to show a table in SQL Server, right-click on your table name under Object Explorer and click on Select top 1000 rows.

4. Once you click Select Top 1000 Rows, you can see the table with added values in SQL Server Management Studio.

Approach-2: Using SQL Create Table Query

Let us see an example of how to create a table in sql server Management Studio using Query.

A basic table in SQL Server is created by first defining the name of the table, creating its columns, and specifying the data type for each column.

To create a table in SQL Server Management Studio using the T-SQL query, we have to use the CREATE TABLE statement, which creates a new table. It has the following syntax.

CREATE TABLE table_name( 
   column1 datatype, 
   column2 datatype, 
   column3 datatype, 
   ..... 
   columnN datatype
);

1. First, connect to the Database Engine using database access credentials.

2. Now go to the Menu bar options and click on New Query or click CTRL + N. It will open an empty SQL file.

Below are the details we have taken for the Employee Details tables. Once you type the query, click the Execute option. If there is no error, you will get the message as commands completed successfully.

Create Table EmployeeDetails(
Empid int,
FirstName varchar(20),
Lastname varchar(20),
Age int,
Role char,
salary money
)

Note – If you want to see it in table format, use the below command and check.

execute sp_columns EmployeeDetails

By doing this, the table has been created in SQL Server.

Add Values in SQL Query

Once you create a table through an SQL query, it’s time to add its values. Here, we are going to use the INSERT INTO statement, which is mainly used to add one or more rows to the target table or view in SQL Server.

Once the table is created, we add the values for the column we have created.

Create Table StudentDetails(
Stuid int,
FirstName varchar(20),
Lastname varchar(20),
Age int,
)

Insert into StudentDetails (Stuid, FirstName, Lastname, Age)
            values (12, 'william', 'shakes', 18);

Once the query has been successfully executed, we will get the following message.

To see the table, use the select query below. The output will be given below.

Select* from StudentDetails

Conclusion

So, in this SQL Server tutorial, we have learned what SQL Server Tables is, How to create a table in SQL Server Management Studio, and How to add columns to the table in SQL Server. Also covered is the show table in sql server.

You may also like:

• Find Store Procedure in SQL Server by Table Name
• SQL Server Left Outer Join Multiple Tables
• SQL Server Inner Join Multiple Tables