C # SqlConnection Querying Temporary Tables

Question

I have a temporary Employee table with an EmployeeHistory as its history table.

In C #, I use SqlConnection to query data from SQL Server for the entire employee history.

 var data = Conn.ExecuteReader("select * from Employee e FOR SYSTEM_TIME ALL WHERE e.Id=15"); 

This causes an error:

Incorrect syntax near FOR

So, how do we query temporary table history data in C # using SqlConnection ?

Answer 1

The problem is that you are using the alias of table e and therefore the error. Do not think that you can use a table alias. change it to

 select * from Employee FOR SYSTEM_TIME ALL WHERE Id=15 

If you check the Data Query documentation in the system table with the system version (OR) Temporary tables, you will see that the syntax does not show the use of the table alias at all. Rather, you will have to use the entire table name.

See this related post, and also Why does the FOR clause not work with an alias in SQL Server 2016 with temporary tables?

Answer 2

To clarify if you need aliases and FOR SYSTEM_TIME, use the following syntax:

 var data = Conn.ExecuteReader("select * from Employee FOR SYSTEM_TIME ALL e WHERE e.Id=15"); 

Answer 3

 SELECT name, compatibility_level FROM sys.databases; 

Only works if your database is at level 130 or more

Answer 4

If you really want to use an alias, for example, if your request is rather complicated and you want to format it better, you can do it in 2 steps using CTE:

 with _data as ( select * from Employee for system_time all where Id=15 ) select * from _data as d 

OR

 with _data as ( select * from Employee for system_time all ) select * from _data as d where d.Id=15