How Do You Create a Table Using SQL (with Examples)?

If you’re new to Microsoft SQL Server, one of the first things you’ll want to learn is how do you create a table using SQL. Tables are the most important part of every database – they are the place where your data lives. In this post, I’ll show you how to create a table step-by-step, explain how to create a backup table and a temp table in SQL, and answer a common question: how long does a temp table last in SQL.

How Do You Create a Table Using SQL?

Creating a table in SQL is easy once you know the basic structure. You simply need to decide what columns your table will have and what type of data each column should store.

Here’s a simple example:

CREATE TABLE Warehouses (
    WarehouseID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100),
    CreationDate DATE
)

In this example:

• WarehouseID stores whole numbers and serves as the primary key.
• Name and Address store text.
• CreationDate stores dates.

Once you run this command, your table is ready to store data. Here is what it looks like in SSMS:

How to Create a Backup Table in SQL

Sometimes you need to quickly create a backup of a table (maybe before running updates or deleting records). The easiest way is to create a copy of the table structure and its data.

Here’s how to do that on our Products table in the Northwind database:

SELECT *
INTO Products_Backup
FROM Products

This creates a new table called Products_Backup with the same columns and data as the original Products table:

If you only want to copy the structure (without the data), you can add a WHERE condition that always returns false:

SELECT *
INTO Products_Backup
FROM Products
WHERE 1 = 0

Now you have an empty backup table ready to fill when needed.

How to Create a Temp Table in SQL

A temp table is a table you can use temporarily within a session. It’s useful for storing intermediate results without cluttering your main database.

To create a temp table, just use a hash (#) before the table name:

CREATE TABLE #TempOrders (
    OrderID INT,
    Amount DECIMAL(10, 2),
    OrderDate DATE
)

SELECT * FROM #TempOrders

Here is how it looks in SSMS:

You can insert and query data in a temp table just like a regular table. The key difference? Temp tables are automatically deleted when your session ends.

How Long Does a Temp Table Last in SQL?

This is a common question: how long does a temp table last in SQL?

The answer is simple: a temp table lasts as long as your SQL Server session or connection is active. Once you disconnect or close the session, SQL Server automatically drops (deletes) the temp table. If you want to remove it earlier, you can manually drop it:

DROP TABLE #TempOrders

Let’s see this code in SSMS:

For multi-session scenarios, you can also create global temp tables using double hashes (##GlobalTempTable), which stay available until all sessions that use them are closed.

Final Thoughts

So, how do you create a table using SQL? As you’ve seen, it’s a simple process that lets you build structures for storing your data. In this post, we’ve also covered:

• How to create a backup table in SQL using the SELECT INTO method.
• How to create a temp table in SQL for temporary data storage.
• How long does a temp table last in SQL, so you know when your temp tables disappear.

With these basics, you’re ready to start creating tables and organizing your data in Microsoft SQL Server.

Let me know if you’d like to see advanced table examples like using constraints or indexes!

Also, make sure you check my previous SQL blog post on What Is DELETE and TRUNCATE in SQL? And if you want to learn more about tables in SQL Server, visit this page by Microsoft.

If you would like to learn more about programming SQL queries, make sure you buy this book: T-SQL Fundamentals (Developer Reference)