How To Retrieve Data From SQL Server Temp Table Manually?

• By Aakshit
• 13-02-2020
• Misc

On the updated version of SQL i.e., SQL server 2016 Microsoft launched the Temporal Tables which is also known as System-Versioned Temporal Tables. It is a built-in feature that supports the gathering of information about the data stored inside the tables. Temporal tables are very useful as they provide deep information about the data files and any change which is reflected on the table than just showing the current data in the present time slot. So, in this technical guide, we are going to discuss all the information on the “SQL server temp table. So, don’t miss any step and follow us line-by-line and continue your reading.

What is a Temp table in SQL server?

A temp table or Temporal Table is a user table that is designed to store a full history of changes reflected on the data in the database and this record of change in data allows inspection by the admin side or the DBA at any time.

The temp tables could be very useful in some cases to keep the temporary database in SQL Server. If you are connected to the SQL database server, then the temp table will exist until you close the client or manually destroy the table.

Restore Data with SQL Server Temp Table

To store the data with the help of the SQL server is possible by finding the state of data at any particular instance of time. Recovering the data files or records is very helpful especially for the case when you need to recover select or reconstruction of an entire table surfaces.

Given below is the steps you need to follow to recovery:

Review changes Made to the Table

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate=’2020-01-10 01:00:00’
SET @EndDate=’2020-01-13 22:45:59’
SELECT * FROM [Users] FOR system_time between @StartDate and @EndDate
WHERE Name = ‘Pollard’

Once your query was executed then you’ll be given with a time point informing if the record was changed or deleted. Now process to recover the record that was missing or changed proceed to the next step.

Create a query to Recover a Record using AsOF

SELECT CDC_DemoID, name, Address, City
FROM [Users] FOR SYSTEM_TIME AS OF ‘2020-13-01 10:20:00’
WHERE Name=’Pollard’

The above query is used to recover the table record from a specific point of time by using the sub-clause AsOF.

Create a Temp Table to Insert the Record

INSERT INTO #TempTable (CDC_DemoID, name, Address, City)
SELECT CDC_DemoID, name, Address, City
FROM [Users] FOR SYSTEM_TIME AS OF ‘2020-13-01 10:20:00’
WHERE Name=’Pollard’

Once the query executes, you’ll be given with a time point informing if the record was deleted or a change was made to the table.

Set the identity Insert ON and insert the Deleted Record and Disable Identity Insert then

SET IDENTITY_INSERT [Users] ON
INSERT INTO [Users] (CDC_DemoID, name, Address, City)
SELECT CDC_DemoID, name, Address, City
FROM #TempTable
SET IDENTITY_INSERT [Users] OFF

With the help of the above query, we have first enabled the identity insert for [dbo].[Users], If the table has an identity. Then inserts the deleted record in the table by fetching it from the temp table we created and finally disabled the identity insert by setting the status to OFF.

Verify the Restored Record

It is important to verify whether the restored records have been recorded successfully or not.

SELECT * FROM [Users] WHERE Name=’Pollard

By using the query given above, you can verify that the deleted record is now restored and available again for future use.

Last Words

In this technical guide, you will get to know about the brief knowledge of what is temp table in the SQL server and how to retrieve data from the SQL server temp table. Although these methods are quite long so, to skip all these steps you can take the help of the SQL Database Recovery tool. Here mobile application development companies are always ready to provide you best knowledge about mobile app technology and apps like Snapchat planets.