How to create a table in sql server management studio (Detailed guide)

In this SQL Server 2019 tutorial, we will discuss what is SQL Server TablesHow to create a table in SQL Server Management Studio, and How to add columns to the table in SQL server 2019, and will cover the below topic.

  • What is a table in SQL server 2019
  • How to create a table in SQL server management studio
  • How to create a table in SQL server management studio using the T-SQL query
  • How to insert data into a table in Microsoft SQL server management studio
  • How to add columns to table in SQL server 2019
  • How to create a table in SQL server with primary key
  • How to create a table in SQL server from another table
  • How to create a table from another table in SQL server with data
  • How to create a table from another table in SQL server without data
  • How to create a table in SQL server from excel

What is a Table in SQL Server 2019

  • A Table in SQL Server is defined as a database object which is used to store data in a database. Tables stores data in a logically organized row-column format much similar to how we store data in a spreadsheet.
  • The SQL server tables can also be defined as a collection of rows and columns, where each row represents a unique record and each column represents a field in the record. Let’s take an example of a student table created and maintained by a school, Here rows can represent each student record, and columns represent student data such as Name, Age, Standard, etc.
  • The number of tables in a SQL Server database is only limited to the number of objects allowed in a particular database instance which is 2,147,483,647. And the number of rows in a standard user-defined database is only restricted to the storage capacity of the machine whereas the number of columns is limited to 1024.
  • We can also control the type of data and also a range of data to be accepted by the table, by assigning properties to the table and the columns within the table.
What is a Table in SQL Server 2019
Table in SQL Server

Read: How to Create a Database in SQL Server 2019

How to create a table in SQL Server Management Studio

We can create a table in SQL Server Management Studio either by using Graphical User Interface options or by creating a SQL query file and executing it.

Now let’s see the steps to create a table in SQL Server Management Studio using Graphical User Interface(GUI)

Step 1. The first step is to start the SQL Server Management Studio (SSMS) and connect with the Database Engine and for this enter the database access credentials and click on “Connect” to connect to the database instance.

create a table in SQL Server Management Studio
Connect to Database Engine

Step 2. The next step is to create a Database, so If you haven’t already done, refer to our previous post. In which a database named sqlserverguides was created for demonstration.

Step 3. Now go to the “Object Explorer” and click on the plus sign (+) just before the database name to expand the database directory.

database in sql server management studio
Expand the Database Directory

Step 4. Now from the list right-click the “Tables” directory and click on the New> Table…” option. It will open a new empty table file in SQL Server Management Studio.

creating new table in ssms
Creating New Table

Step 5. In the Table file, we can specify Column Name, Column Data Type and a property Allow Null for allowing nulls for each column or not.

create a new table in sql server management studio
New Table Fields

Step 6. Now let’s try to create a simple student information table and enter the following fields in the table

  • Name- To specify the name of the student
  • Age- To specify the age of the student
  • Contact- To specify a contact number
How to create a table in SQL Server Management Studio
Student Information Table

Step 7. After adding the Column Name and Data Types, go to the menu bar and click the “Save” option or just click “Ctrl + S” to save the table in the database.

create a new table in sql server management studio
create a new table in sql server management studio

A new window will pop up in which we can specify the table name and then click “OK” to save the table.

create a new table in microsoft sql server management studio
Specifying Table Name

Step 8. Now expand the Tables directory in Object Explorer, the newly created Students table will be listed in the Tables directory.

how to create table in sql server management studio
New Student Table

This is how to create a new table in microsoft sql server management studio.

Read: How to export data from SQL Server to Excel

How to create a table in SQL Server Management Studio using T-SQL 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.

For creating a table in SQL Server Management Studio using the T-SQL query, we have to use the CREATE TABLE  statement which is used to create a new table. And it has the following syntax.

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

So let’s try to create an employee information table using T-SQL query in SQL Server Management Studio

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

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

create a table in SQL Server Management Studio using T-SQL
New Query

Step 3. Now in the empty SQL file enter the following CREATE TABLE statement and click on “Execute” from the menu bar to execute the query.

create sql table query in t-sql
T-SQL Query to create a Table

In the above query, we are telling the database system to create a table with the name “employee” and create 5 columns in it with the following data types.

  • empID- It is an integer field used to uniquely define an employee record.
  • Name– It is a variable-length string used to define the name of an employee.
  • Age– It is an integer field used to define the age of an employee.
  • Address– It is a fixed-length string used to define the address of an employee.
  • Salary– It is an exact fixed-point number used to define the salary of a particular employee.

Step 4. We may check if your table was successfully created by glancing at the message displayed by SQL Server Management Studio, if not, we may use the command below.

exec sp_columns employee
how to create a table in sql server management studio using query
create a table in sql server management studio using query

We can now see that employee table is available in your database.

Read: SQL Server 2019 New Features

How to insert data into a table in Microsoft SQL Server Management Studio

Now just like the creation of SQL Server Database and Table we can either use Graphical User Interface (GUI) options or use Transact-SQL query to insert data into a table.

Using Graphical User Interface(GUI) Options

Now lets first explore the GUI options to insert data into a SQL Server Table.

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

insert data into a table in Microsoft SQL Server Management Studio
Table Edit Option

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

Adding rows into table using sql server management studio
Adding Data into a Table

Step 3. We can also view the table data, right-click the table from Object Explorer and click on “Select Top 1000 Rows“.

selecting top 1000 rows using sql server management studio
Selecting 1000 rows from the top of the Table

It will automatically execute a query to return 1000 rows of data, starting from the top of the table.

how to create a table in sql server management studio using query
1000 rows from the top of the Table

Using Transact-SQL Query

Now to insert data into a table using Transact-SQL query in SQL Server, we are going to use the INSERT TO statement which is mainly used to add one or more rows to the target table or view in SQL Server. This can be accomplished by using constant values in the INSERT INTO query or by specifying the table or view from which the entries will be copied.

Step-1. In SQL Server Management Studio, go to the Menu bar options and click on “New Query” or click “CTRL + N“. It will open an empty SQL file in the Management Studio.

how to create a table in sql server management studio using query
New Query Option in SQL Server Management Studio

Step-2. Now enter the following query in the empty SQL file.

INSERT INTO <Table_Name> VALUES ('Value-1', 'Value-2', 'Value-3'....),('Value-1', 'Value-2'....)
query to insert data into table
Query to insert single row

Step 3. After writing the query, go to the Menu bar and click on the “Execute” option to execute the written query.

create a table in sql server management studio using query
Execute Option

Step-4. Once the query has been successfully executed we will get the following message

insert data into sql server table
Successfull Query Execution

Read: Advanced Stored Procedure Examples in SQL Server

How to add columns to table in SQL Server 2019

If you want to work either as a Data Administrator or as a Database developer, you need to be familiar with the ways to add a column in an existing table in SQL Server 2019.

So generally, we use the ALTER Table statement, which is used to modify a table definition by altering, adding, or dropping columns and constraints. And, it can also be used to rebuilds partitions, as well as enabling and disabling constraints and triggers.

But by using ALTER TABLE statement, the columns are automatically added at the end of the table. So if we want the columns to be in a specific order, we have to use SQL Server Management Studio. So we will cover both the methods below.

Adding Columns using SQL Server Management Studio

Step 1. Go to “Object Explorer”, right-click on the table in which you want to add a new column, and select the “Design” option. A table design page will be viewed.

adding column to table using sql server management studio
Selecting Design Option

Step 2. Now on the Design page, first set the cursor on an empty cell and then enter the column name and also set the data type for the corresponding column.

adding new column using ssms
How to add columns to table in sql server 2019
Adding New Column

Step 3. After adding the new column details, click on the Save” option from the menu bar or click “Ctrl + S“. After this, a new column will be added to the table.

How to add columns to table in sql server 2019
Saving the Table

Adding Columns using Transact-SQL

The Alter Statement is used to add a new column to an existing table in SQL without deleting or dropping it. And the syntax for it is as follows.

ALTER TABLE table_name ADD column_name column_definition;
query to add new column in sql server table
Alter Table Query

In the above example, we are using the ALTER TABLE command to add a new column named Zip_Code to the info_1 table. And we are defining the data type of the Zip_Code column to be an integer and allowing it to be NULL.

Read: View line numbers in SQL Server Management Studio

How to create a table in SQL Server with primary key

Let us see, how to create a table in SQL Server with primary key.

In a SQL Server, a primary key is a field in a database table that uniquely identifies each row or record. The Primary keys should have unique values and NULL values are not allowed in a primary key column which simply means that if a primary key is defined on any field(s) in a table, no two records can have the same value for that field(s).

A table can only have one primary key, which can be made up of one or more fields. We can also select multiple fields as the primary key then it will be called a composite key.

We can use SQL Server Management Studio or Transact-SQL to create a primary key in SQL Server 2019. When you construct a primary key, it automatically generates a unique clustered index, or a nonclustered index if you specify one.

Using SQL Server Management Studio

Step 1. First, right-click the table to which you wish to add a primary key constraint in Object Explorer and select “Design”.

Step 2. Select the database column you want to use as the primary key in Table Designer by clicking its row selector. And now right-click the row selector for the column and select “Set Primary Key”.

Note- Please make sure that the selected field is not nullable before selecting it to be primary key.

setting primary key in sql server
Selecting Primary Key

In the row selector, a primary key symbol identifies a primary key column.

create a table in SQL Server with primary key
Identifying Primary Key

Using Transact-SQL

Now there can be two situations:

  1. When we want to create a new table in SQL Server with a primary key field.
  2. When we want to update or alter an existing table in SQL Server with a primary key field.

Each of these situations are described below.

1. Creating a primary key in an existing table

Now to modify an existing table in SQL Server Database we will be using the ALTER TABLE command with PRIMARY KEY constraint in the following manner.

ALTER TABLE table_name ADD PRIMARY KEY ( column_name );
query to add primary key in a sql server table
Query to add primary in existing table

In the ablove example we are modifying the info_1 table and making “id” column as a primary key.

2. Creating a primary key in a new table

Now to create a new table in SQL Server Database we will be using the CREATE TABLE command with PRIMARY KEY constraint in the following manner.

CREATE TABLE table_name(
column_1 data_type1 NOT NULL PRIMARY KEY,
column_2 data_type2,
);
create table in sql server with primary key
Query to create a new table with primary key

In the above example, we are creating a table Details with four columns and we are setting the ID field to be the not null primary key.

How to create a table in SQL server from another Table

let’s first understand the need of creating a table from another SQL server table, As a Database administrator or as a developer there be situations where we might wish to make an exact copy or clone of an existing table to test or conduct a task without impacting the original table.

The following section describes how to create a table from another table with or without data in sql server.

1. Creating a table from another table in SQL Server with data

Let’s assume we already have a table named info_1 with the following rows and columns.

create a table in SQL server from another Table
create a table in SQL server from another Table

We can easily copy the table data into a new one using the following query.

SELECT column-1,column-2,.... INTO new_table FROM old_table;
how to create a table in sql server from another table
Creating a table from another table in SQL Server with data

In the above example, we have copied the whole data from the info_1 table to the new_info table, but we can also copy specific columns by defining column names instead of * in the query.

We can also confirm the result using the SELECT query.

create a table from another table using sql
Newly Created Table

2. Creating a table from another table in SQL server without data

Now for creating a table from another table without data in SQL server 2019, we have to use the WHERE clause in the above-mentioned query. And we also have to define a false condition in the WHERE clause.

SELECT column-1,column-2,.... INTO new_table FROM old_table WHERE 1=2;
copying a table from another table without data
Query to copy table without data

In the above example, we have copied all column names from the info_1 table to the new_info table, but we can also copy specific column names by defining column names instead of * in the query.

We can also confirm the result using the SELECT query.

copying a table from another table without data in sql server
Query to Comform the result

Read: SQL Server Convert String to Date

How to create a table in SQL Server from Excel

The best way to create a SQL Server table from an excel file is to use the SQL Server Import and Export Wizard. The SQL Server Import and Export Wizard provides the option to import data from an excel file and we can even use it by using SQL Server Management Studio.

Using SQL Server Import and Export Wizard, We can either directly copy the whole data from an excel file to a table in a database or we can generate a query out of it.

  1. For the demonstration, we are using small set of excel data which is shown below.
creating table from excel in sql server
Sample Excel Data

2. Now we should choose the database in which we want to store the newly created table. So we are using an empty database named sqlserverguides.

how to create a table in sql server from excel
Database to store the new table

Step 1. Now open SQL Server Management Studio, right-click the database in which you want to store the new table, select the “Tasks” option and click on “Import Data..“. After this SQL Server Import and Export Wizard welcome window will appear.

how to create a table in sql server from excel
Selecting Import data option

Step 2. Now in SQL Server Import and Export Wizard window click “Next” to move to the next option.

What is a table in sql server 2019
SQL Server Import and Export Wizard Welcome Window

Step 3. Now on the next page, we need to select the Data Source option to be “Microsoft Excel” and also select the Excel file path which represents the path of our excel file. And after selecting these click “Next“.

create a table in sql server from excel
Selecting Data Source

Step 4. On the next page, Choose a Destination, we have to pick Microsoft SQL Server as our destination and for this, we have to pick one of the data providers from the list that connects to SQL Server. In this example, we have selected “.Net Framework Data Provider for SQL Server”.

After making the selection the page will display a list of properties and there are three important properties that we need to set

  1. Data Source– In this, we need to define the Server Name.
  2. Integrated Security; or User ID and Password- Through this, we have to provide valid login credentials.
  3. Initial Catalog– In this, we need to define the Database Name in which the table will be saved.
create a table in sql server 2019 from excel
Choosing the destination source

Step 5. Now on the next page, we need to specify if we want to copy the entire table of source data or we want to write a query. So for this particular demonstration, we are selecting the copy option.

How to create a table in sql server 2019 from excel
Choosing the copy option

Step 6. On the next page, we need to pick tables that we want to copy from the data source. Then we have to map each selected source table to a new or existing destination table. And in our example, there is only one table in the data source.

how to create a table in sql server from excel
Selecting Source Tables

Step 7. Now on the next page, we can leave Run immediately enabled to copy the data as soon as you click Finish on the next page. 

create a table in sql server 2019
Run immediately Option

Step 8. On the next page, we will get a summary of what the wizard is going to do. Click Finish to run the import-export operation.

how to create a table in sql server 2019
Wizard Summary

Step 9. On the final page, the wizard will show the task execution and we have to wait till the time gets completed. After successful task execution will able to see a new has been added to our database.

final execution in sql server import wizard
Final task execution page

Now we can see a new table and it also contains the excel data.

create a table in sql server management studio
New tables addes into the databse
copied data from excel to table in sql server
New Table Data

So in this SQL Server 2019 tutorial, we have learned what is SQL Server TablesHow to create a table in SQL server management studio, and How to add columns to the table in SQL server 2019, and will also covered the below topic.

  • What is a table in SQL server 2019
  • How to create a table in SQL server management studio
  • How to create a table in SQL server management studio using the T-SQL query
  • How to insert data into a table in Microsoft SQL server management studio
  • How to add columns to table in SQL server 2019
  • How to create a table in SQL server with primary key
  • How to create a table in SQL server from another table
  • How to create a table from another table in SQL server with data
  • How to create a table from another table in SQL server without data
  • How to create a table in SQL server from excel