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 Tables, How 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.

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.

Step 2. The next step is to create a Database, so If you haven’t already done, refer to our previous tutorial on creating a database in an SQL server. 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.

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.

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.

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

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.

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

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

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

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.

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.

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

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

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.

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

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

It will automatically execute a query to return 1000 rows of data, starting 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.

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'....)

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

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

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 rebuild partitions, as well as enable and disable 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 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.

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.

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.

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;

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.

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.

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

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 );

In the above 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,
);

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.

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;

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.

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;

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.

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.

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.

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.

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

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“.

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.

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.

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.

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. 

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.

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.

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

So in this SQL Server 2019 tutorial, we have learned what is SQL Server Tables, How 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

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
• How To Update Table Using JOIN in SQL Server
• SQL Server Trigger on Delete Insert Into Another Table