Have you ever needed a temporary table in SQL for some quick calculations or data manipulation, only to realize it's not as straightforward as you thought? Let's dive into creating temporary tables in SQL, also known as temp tables, with a focus on W3Schools-style simple and easy examples.
Before we begin, it's important to note that temp tables are valid only within the session in which they're created. Once the session ends, the temp table is automatically deleted. Now, let's jump right in and create our first temp table.
Creating a Temporary Table in SQL
The basic syntax for creating a temp table in SQL looks like this: `CREATE TABLE #temp_table_name (column1 datatype, column2 datatype, ...);`. The '#' symbol before the table name indicates that it's a local temp table, which is visible only to the current session.
Let's create a simple temp table with some quartz data for our examples:
```sql CREATE TABLE #Quartz ( Id INT, Color VARCHAR(10), Price DECIMAL(6,2) ); ```
Populating the Temp Table
Now that we have our temp table, let's populate it with some data:
```sql INSERT INTO #Quartz (Id, Color, Price) VALUES (1, 'Rose', 0.99), (2, 'Diamond', 99.99); ```
To verify that our data has been inserted correctly, we can use the `SELECT` statement:
```sql SELECT * FROM #Quartz; ```
Using the Temp Table in Queries
Temp tables are particularly useful when you need to perform calculations or manipulate data before inserting it into a real table. Let's calculate the total price of all quartz in our temp table:
```sql SELECT SUM(Price) AS TotalPrice FROM #Quartz; ```
Using `#` prefix, we can create a local temp table within a session. But what if we want a temp table that's visible to all sessions running on the same server? Let's explore this next.
Creating a Global Temp Table
A global temp table is one that's visible to all sessions running on the same server, but it's still deleted once it's no longer in use. The syntax is the same as a local temp table, but with a double hashtag (`##`) prefix instead:
```sql CREATE TABLE ##GlobalQuartz ( Id INT, Color VARCHAR(10), Price DECIMAL(6,2) ); ```
From here, you can insert data and perform various operations just like you would with a local temp table. The difference is that this temp table is accessible by all sessions.
Limiting Temp Table Use
While temp tables can be incredibly useful, remember that they consume server resources. Always ensure that you're only using them when necessary, and delete them when they're no longer needed:
```sql DROP TABLE #Quartz; DROP TABLE ##GlobalQuartz; ```
Creating and using temp tables is a simple way to perform quick data operations in SQL. Whether you're calculating totals, sorting data, or performing other temporary manipulations, temp tables can be a valuable tool in your database arsenal.
Now that you've mastered creating and using temp tables in SQL, it's time to experiment and find out how they can streamline your own data processing tasks. Happy coding!