In this tutorial, I’m going to show you how to perform the Basic SQL table operations which include creating and deleting tables. We’ll also discuss inserting, removing, and updating records in a table.
In addition, we also use the term CRUD which stands for Create, Read, Update, and Delete for the basic table operations of SQL.
If you’ve ever worked using Excel, just visualize the Excel file as your database, the tables you create in SQL as the Excel pages, and the records as the rows in your Excel sheet.
SQL Table Operations: CREATE TABLE
Let’s start with the CREATE TABLE command. To create a table, first, we need to type the CREATE TABLE command. Then enter the table name. Please note that the table name you’re going to use should not be present in the database, or else, it’ll not be created.
After that, we need to add the open and close parentheses. Then, we’ll be adding the Column Name and Data Types between the parentheses.
We can also create a table from a query. We can do this by using the command SELECT * INTO person.Persons_copy FROM person.Persons. This command will create a table named person.Persons_copy with the same structure and records from the person.Persons table.
Manipulating Records In SQL
Once we have created a table, we can now use the INSERT command to enter a record. The highlighted commands below will enter the values into the table named dbo.courses.
In case you’ve made a wrong entry and you want to delete it, you can use the DELETE command to do that. Using the command below, the records with ids greater than or equal to 1000 will be deleted in the dbo.courses table.
It’s important to use the WHERE command in this operation as it helps you to specify which record should be deleted, or else this command will delete everything from the table that you’ve specified.
If you want to delete everything in a table, you can use the TRUNCATE TABLE command. The highlighted command below will clear the records inside the table dbo.courses.
Updating a record is very simple. We use the UPDATE command for doing this operation. The highlighted command below will update the name “Dax” into “DAX” inside the dbo.courses table.
As you noticed, we used the SET command in the example to identify which column and value should be updated. We also added the WHERE command to point out the existing record that we’ll be updating in the dbo.courses table.
SQL Table Operations: DROP TABLE
We can also drop tables simply by using the DROP TABLE command. The highlighted command in this example shows how to delete a specified table.
This function is similar to the TRUNCATE TABLE command. However, the main difference between these two is that the TRUNCATE TABLE command is used only for clearing out a table. On the other hand, the DROP TABLE command is used to delete a specific table together with its records.
Let’s now try using these SQL commands in Microsoft SQL Server Management Studio.
Creating A Table In SQL
Let’s start with creating a table. For example, we want to create a table named dbo.Names.
Right now I’m working in the AdventureWorks2012 database. This is a backup database provided by Microsoft on their documentation website.
On the right side, when we click the + icon beside the Tables folder, we can see that there’s no existing table in the Tables folder that has the table name dbo.Names.
Now let’s run the command below to create the dbo.Names table.
After clicking the Execute button, it’ll show a message saying “Commands completed successfully.”
Next, refresh the Tables folder. Right-click on it then select Refresh.
Upon refreshing, we can see the dbo.Names table added under the Tables folder.
The dbo.Names table is currently empty. Right-click on it and select the Select Top 1000 Rows from the options to open it.
Upon opening, we’ll see that it doesn’t have any record and it only shows 3 columns which are ID, FirstName, and LastName.
Creating A Temporary Table In SQL
A Temporary Table always starts with #. This is very useful in finding and resolving potential errors and writing SQL queries because it is not permanently stored in the database. When we close a session, the table will be deleted. You can create a Temporary Table by using the highlighted command below.
From here we’ll use the #Names table to run other commands.
Selecting A Table In SQL
After creating the #Names table, we’re now going to check its records by running the following highlighted command.
But since we haven’t entered any record yet, the table will only show the 3 columns.
Dropping A Table In SQL
Now if we want to drop or remove the #Names table, we’ll use the DROP TABLE command and select the #Names table.
After that, run the SELECT * FROM #Names command and it’ll show a message like this.
This is because we deleted the #Names table.
Inserting A Record In A SQL Table
Before proceeding, we’ll recreate the #Names table then insert a record into that table using the command below.
This command means we are inserting the values (1, ‘Dean’, ‘Jones’) from the columns (ID, FirstName, LastName) into #Names.
When you see a message similar to the example below after running this command, it means that the record has been added to the table.
Now run the command SELECT * FROM #Names and you should see the record that we just inserted.
Inserting Multiple Records In A Table
To insert multiple records in a table, you just need to create multiple values. But make sure to separate them with “,”. Use the command below as reference.
If we run the command SELECT * FROM #Names again, we’ll now have a total of 4 records in the #Names table.
Updating A Record In A Table
Next, let’s update a record in the #Names table. Let’s try changing John’s ID into 10. First, we’ll use the UPDATE command then select #Names. After that we’re going to add the following commands: SET ID = 10 and WHERE FirstName = ‘John’ to select John using his first name and set its ID to 10.
The command above will update John’s ID into 10.
Now run the command SELECT * FROM #Names again to check if the record was successfully updated.
As a result, John’s ID has been updated into 10.
Deleting A Record In A Table
This time we’ll delete a record in #Names table. For example, we want to delete a record with an ID value of 3.
Let’s use the commands DELETE FROM #Names then WHERE ID = 3 to do this.
After deleting, run the command SELECT * FROM #Names again and we’ll notice that we only have 3 records left in the #Names table.
Emptying A Table In SQL
Next, we’ll clear out the contents of #Names table. We’ll use the command TRUNCATE TABLE #Names in this operation.
Once we’ve executed the TRUNCATE TABLE command, use the command SELECT * FROM #Names and it’ll show that the #Names table is now empty.
Copying A Table
Finally, we’ll learn how to copy a table. To do this, we need to identify which table we would like to copy. For example, we want to make a copy of the Person.Person table. In this example, we’ll use the command SELECT * INTO Person.Person_copy FROM Person.Person to make a copy of the Person.Person table together with its records.
Next, we’ll make a copy of the Person.Person table with only 1 record. For example, we want to copy only the record with BusinessEntityID value 1.
To do this, we must add the WHERE command next to the FROM Person.Person command. Then type the column name BusinessEntityID and set the value to 1.
After running the command, it’ll show a message which indicates that we have successfully created a copy of the Person.Person table. The name of the table is Person.Person_copy with a record that has the BusinessEntityID value of 1 from the Person.Person table.
Let’s select the Person.Person_copy table using the following command.
Using these steps, we were able to create the same number of columns from the Person.Person table and create the Person.Person_copy table.
***** Related Links *****
Common SQL Table Expressions For Power BI Users
Subquery In SQL For Power BI Users
Common SQL Functions: An Overview
Conclusion
To sum up, we’re able to use the basic SQL table operations for various purposes. We learned how to create a table; insert, update and delete records in a table; and create a copy of a table.
In addition to that, we also discussed how to create a Temporary Table that we can use for testing out commands and finding or fixing potential errors. Keep in mind that all of the operations we did in the Temporary Table can also be done in the Physical Table. Just remember that once we close a session, the Temporary Table will no longer exist.
If you’d like to know more about this topic and other related content, you can certainly check out the list of relevant links below.
All the best,
Hafiz
