Temp Tables question

Question

Post reply

Temp Tables question

rjjh78

SSCrazy

Points: 2784

March 31, 2022 at 3:29 pm

#4008866

Is it possible to create a temp table when setting a query?

For example:

DECLARE 
@Category    varchar(100), 
@SubCategory varchar(100),
@Query       varchar(1000)

SET @Query = 'SELECT TestName FROM tblTest WHERE CategoryID = (CategoryID =' +  @CategoryID + ')  AND (SubCategory IN (' + @SubCategory + '))'

EXEC(@Query)

How could I place the results into a temp table?

Many thanks in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

Here's an example

DECLARE @Query VARCHAR(1000) = 'SELECT TOP (10) object_id, name FROM sys.columns';

DROP TABLE IF EXISTS #SomeTab;

CREATE TABLE #SomeTab
(
    object_id INT
   ,name NVARCHAR(128)
);

INSERT #SomeTab
(
    object_id
   ,name
)
EXEC (@Query);

SELECT *
FROM #SomeTab st;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

rjjh78 wrote:

Is it possible to create a temp table when setting a query?
For example:
DECLARE 
@Category    varchar(100), 
@SubCategory varchar(100),
@Query       varchar(1000)

SET @Query = 'SELECT TestName FROM tblTest WHERE CategoryID = (CategoryID =' +  @CategoryID + ')  AND (SubCategory IN (' + @SubCategory + '))'

EXEC(@Query)
How could I place the results into a temp table?
Many thanks in advance.

I see no reason to use Dynamic SQL in this case. In fact, what you've posted is actually dangerous code because it concatenates user character based variables into the dynamic SQL making it extremely susceptible to SQL-Injection.

Yep... I do realize it's "just" and example but other readers need to be warned and maybe you do to because I find that most examples are born from the limits of what someone knows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)