I am using SQL Server. My question is, can I create a temporary table without defining its columns? or is there a dynamic way to create a temporary table from a SELECT Column1, Column2 FROM DBO.TBLDATACOPY
?
The goal is to create a temporary table to store the data returned by a SELECT Column1, Column2 FROM DBO.TBLDATACOPY
that is executed using a EXECUTE sp_executesql
. I cannot do the following SELECT * INTO #TBCOPY FROM (SELECT *FROM DBO.TBLDATACOPY)
because the table #TBCOPY
is destroyed at the end of the sp_executesql
.
If you just have to use a SELECT INTO:
Run this example and tell me if it comes close to what you need, obviously making it dynamic would not be an option.
There is a rather elaborate way to do it for my taste, I will explain to you.
Normally when we want to create a temporary table dynamically without having to define the
CREATE
first, what we do is:It is not possible to do the same thing with the result of an SP, if we can do a
INSERT INTO
, but clearly for this, we must know the structure and have created the table in the first place. An alternative is to pass the execution of the SP through oneOPENROWSET
that somehow simulates a Table with the results, but to do this, our SQL Server must have a special configuration, so the first thing is to reconfigure it like this:If we have the possibility to maintain this configuration, then we can do:
Obviously modify the connection dsn as appropriate, in this example we are using integrated security, but user/password can be configured. This has its drawbacks, dsn in clear text, an overhead for the use of OPENROWSET, etc. If one can live with those drawbacks, this may be a valid alternative.
Are you using any SP (Stored Procedure)? If so, use your SELECT INTO conventionally, without the initial # (Numeral) and it will create the table, work with it as much as you need, that is, delete it before the SP itself ends. If not, determine a mechanism to remove it during your process so you don't keep the table indefinitely.
You can do it in the following way:
In this way you can define the columns that you want to have in your temporary table, that is, if you want it to contain all the columns of the physical table or just some specific ones, you can also filter the data since you can add WHERE in addition to doing Join with other tables
A CTE can serve you