In SQLServer there are table and variable table and variable table features.
What would be the most advantageous or best option to put in a procedure that inserts the query that was returned from a SELECT , and can store an average of 2 thousand records ?
A temporary table is a real table and can do everything a normal table can, SQL Server will make all possible optimizations in it, especially if it has secondary indexes, can be in transactions, respects the settings of its database anyway , the only difference from the normal table is that it will be discarded at the end of the user session or all users using it.
Variables with tables can be easier to use in some scenarios such as stored procedures and functions. It may even help their own performance (not the query execution). But you almost do not use those things, right? Or are you DBA? : P But there are things that are harder to do with this feature. The variable is a programming resource in the database database, it is not usually used in the normal manipulation of the database, it is a code instrument. Do not use for tables with more than hundreds of rows. Think of it more as a array a bit more sophisticated than a table itself.
As each has a function they offer more advantages in the appropriate scenario. Interestingly, a feature made for programmers is often used by DBAs who like to play code inside the database, and the most made feature for the database itself is preferred by programmers as they program out of the database. / p>
