SQL Basics 1: How to Create Table and Add Data in SSMS
SQL
In this blog post, we delve into the fundamentals of creating tables in SQL Server Management Studio (SSMS), the powerful and user-friendly interface for managing SQL Server databases. Whether you're a seasoned SQL developer or a newcomer, we'll walk you through the step-by-step process of creating tables to store and organize data efficiently. Unleash the potential of your data management skills with our comprehensive guide on table creation in SSMS SQL.
Hello everyone, in this blog post, I’ll show how to create tables in SQL Server Management Studio.
I’m assuming you have already installed SSMS and SQL server in your computer.
If you haven't done it yet:
Download SQL Server Management Studio (SSMS) HERE
You can download Express version for following exercises.
Just to clarify, SQL Server Management Studio is an integrated environment for managing any SQL setup. It provides tools to arrange, monitor, and control instances of SQL Server and databases.
On the other hand, SQL Server is a popular relational database management system that stores and retrieves data as requested by other software applications, like SSMS, on the same computer or on a remote computer.
To summarise, we use the software application SSMS to run queries and SQL server store the data.
If you want to learn more, check out:
What is The Difference Between SSMS and SQL Server?
OK... Let’s start!
1. CREATE TABLES IN SSMS
1. Connect to a server
Open your SQL Server.
The first thing you see, is a window asking to connect to a server. And that is why we downloaded the SQL Express server. Now, we just connect them.
By doing that, we have SSMS setup, and we can start creating our table.
2. Create a database
Click on database -> new database -> name it as SQL_Basics, or any name you desire.
When you open the SQL_Basics database, there’s going to be a lot of stuff, however, for now, let’s stick only to tables.
As you can see, we don’t have any tables. The next step is to create a new table.
3. Create tables
There are 2 ways to do it:
Right click on table -> new table -> table
Or
Use a script or T sequel by clicking on new query.
Next, we will create 2 tables that we will be using for next SQL activities.
Table 1.
Let’s start with our first SQL statement:
CREATE TABLE: Used to create a new table in a database.
For our fist table, we are naming it: EmployeeDemographics, or as you desire.
Make sure to open a parenthesis to specify column names and type of data for each column, and close parenthesis when the table is finished.
For this table, we are creating columns: EmployeeID, FirstName, LastName, Age and Gender as shown below.
NOTE:
Int: Means integer for numbers like 1, 2, 3 for anything numeric.
Varchar: Varchar means a variable length string that can contain letters, number, and special characters.
(50): Specifies the maximum string length in characters.
Click on EXECUTE to create the table.
Then, let’s REFRESH our table and...
...there you go!
Congratulations, you have created your first table!
Now, let’s create our second table, which we will take the exact same steps, however, the new table will have different information.
To get rid of the first table, let’s highlight the information and click on COMMENT.
Table 2.
Start with CREATE TABLE statement, naming it EmployeeSalary, or the name you desire.
Open parenthesis, add desired information, in this case we are using:
(EmployeeID int,
JobTitle varchar (50),
Salary int)
close parenthesis.
EXECUTE to create table,
REFRESH to see table update.
We just created the second table.
Now, let’s open one of our table EmpoyeeDemographics to see what is in there by doing a right click on EmployeeDemographics table, then Select Top 1000 Rows.
As you can see, our table is empty.
So, let's insert data into our new tables.
2. INSERT DATA INTO TABLES:
On the same tab you created tables, you can either highlight + COMMENTS, or delete script as we have already created our tables.
NOTE: If you close tabs, make sure you save then. Otherwise, data will NOT be saved for future access.
Back to inserting data into our tables using another SQL statement:
INSERT INTO: Used to insert new records in a table (specify table),
VALUES: Specifies the values of an INSERT INTO statement
Type statement and find the table you want to insert information. In this case, we are using EmployeeDemographics table.
Use statement INSERT INTO EmployeeDemographics VALUES.
Open parenthesis and let’s select the values we are going to put into this table.
(A number for EmployeeID,
A name for FirstName using ‘Brad’ when refers to varchar,
A last name for LastName using ‘Smith’ when refers to varchar,
A number for age,
The gender using ‘Male’ when refers to varchar)
Close parenthesis.
It will look like:
(1001, ‘Brad’, ‘Smith’, 59, ‘Male’)
EXECUTE to save first roll.
Since we have already added EmployeeID 1001, let's write the rest, so it does not overwrite the same information.
NOTE: You can copy and paste in case you do not want to write the whole thing.
(1002, 'Christian', 'Lopez', 49, 'Male'),
(1003, 'Angelina', 'Miller', 47, 'Female'),
(1004, 'Natalie', 'Lee', 41, 'Female'),
(1005, 'Jennifer', 'Wilson', 54, 'Female'),
(1006, 'Adam', 'Wilson', 56, 'Male'),
(1007, 'Jennifer', 'Roberts', 53, 'Female'),
(1008, 'Tom', 'Harris', 66, 'Male'),
(1009, 'Tom', 'Davis', 60, 'Male')
With all the values ready, EXECUTE again.
Let’s run to see the full table by doing a right click on top of the EmployeeDemographics table, then Select Top 1000 Rows.
We just inserted our core information for our next activities.
Next, let’s do the same with our EmployeeSalary table.
Start with the statement INSERT INTO EmployeeSalary VALUES
(1001, 'Driver', 55000),
(1002, 'Writer', 100000),
(1003, 'Hairdresser', 62000),
(1004, 'Analyst', 98000),
(1005, 'Marketing Coordinator', 78000),
(1006, 'Engineer', 114000),
(1007, 'Cleaner', 55000),
(1008, 'Pilot', 120000),
(1009, 'Secretary', 72000)
Table will look like:
We are good to go for our future Activities!
Thank you for reading and hope you are taking the steps.
The only way to learn, is implementing what you learn 😊
If you like this content and want to learn more, you might also read:
Essential Technical Skills for Data Analysts
SQL Basics 2: SELECT + FROM Statements
SQL Basics 4: GROUP BY and ORDER BY Statements
SQL Intermediate 1: INNER and OUTER Joins
SQL Intermediate 2: UNION Operator:
SQL Intermediate 3: CASE Statement
SQL Intermediate 4: HAVING Clause
SQL Intermediate 5: UPDATE and DELETE Data
SQL Intermediate 6: AS - Aliasing
SQL Intermediate 7: PARTITION BY Clause
SQL Advanced 1: CTEs - Common Table Expressions
SQL Advanced 3: Stored Procedures
SQL Advanced 4: SUBQUERIES - In Select, From and Where
SQL Advanced 5: String Functions - TRIM, LTRIM, RTRIM, REPLACE, SUBSTRING, UPPER, and LOWER