I have a stored procedure that I use to load csv files to SQL. Initially, this will massively insert the file into a global temporary table and then at the end it will do:
select cast(column1 as int) (column1), column2, column3... into table from ##temptable
and then proceed to release the temptable ##
Before "selecting in the table from ## temptable" I run a series of selection queries in the temporary table to determine what the columns should be (and I'm sure this works correctly).
Note that having a table (normal or temporary) before creating the final output table is essential for the stored procedure.
What I would like to know is if it is better to use a global temporary table or if I should use a normal table or does it make no difference?
I have seen several articles on the differences between temporary tables and table variables, but I have not seen anything when it comes to comparing global temporary tables with normal tables and it is difficult for me to test this correctly in my environment.