Using temporary table- How do I pass more than 1,000 parameters in SQL in Clause

I want to use a parameter to pass more than 1,000 parameters in SQL in Clause and created a temporary table for the same:

drop table if EXISTS #k_temp_table
select i.account_value
into #k_temp_table
from 
(
    select distinct acnt.account_value
    from tablea a 
    join tableb b  on b.account_key = a.account_key
    outer apply fngetaccount(a.account_key) acnt
    where b.period_end_dt > 2016-12-31
    group by acnt.identifier_value 
)i

select * from #k_temp_table 
--  gives me all the account numbers I need- approx ~40K values.

Issue- How do I call it in the CTE as a subquery as the SQL script just keeps running and gives no result:

with Total as (
   select xyz...
   from tables abc
   where abc.account_value in (I am trying to use subquery here from #k_temp_table to parametrize account values)
)

I cannot understand the issue and is there another way to do this?

@JoelCoehoorn I tried all approaches but getting following error " Could not allocate space for object 'dbo.SORT temporary run storage: 155990860234752' in database 'tempdb' because the 'PRIMARY' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup." Then I ran this query-'select * from tempdb.sys.allocation_units'- to find my allocation unit limit but is there any other way to do this as I only have read-only access to the database.

the SQL script just keeps running and gives no result:

"Just keeps running" means you wrote a very slow query. Wait long enough, and probably you'll eventually see something. Fortunately, there are a number of ways we can greatly improve performance here.

To start, you could change this to use a JOIN or EXISTS query instead of IN(), and that will already improve performance considerably (especially if you index the temp table):

with Total as (
   select xyz...
   from tables a
   inner join #k_temp_table k on k.account_value = a.account_value
)

or

with Total as (
   select xyz...
   from tables a
   where exists (select 1 from #k_temp_table k where k.account_value = a.account_value)
)

But that's still kind of weak. There's no good reason to use a temp table for this at all. While temp tables can be useful and can even occasionally improve performance, generally they make things slower and more complicated, and are a tool of last resort.

Instead, you should integrate the query to generate the temp table into the CTE directly. For that, you could just copy/paste to a subquery like this:

with Total as (
   select xyz...
   from tables c
   inner join (
       select distinct acnt.account_value
       from tablea a 
       join tableb b on b.account_key = a.account_key
       outer apply fngetaccount(a.account_key) acnt
       where b.period_end_dt >= '20170101'
       group by acnt.identifier_value
   ) d on c.account_value = d.account_value
)

or you could use it as another CTE:

WITH k_temp_table as (
    select distinct acnt.account_value
    from tablea a 
    join tableb b  on b.account_key = a.account_key
    outer apply fngetaccount(a.account_key) acnt
    where b.period_end_dt > 2016-12-31
    group by acnt.identifier_value 
)
, Total as (
   select xyz...
   from tables a
   inner join k_temp_table k on c.account_value = a.account_value
)

Either of these will already likely perform MUCH faster, as you won't need extra RAM for the temp table and can take better advantage of indexes on the source tables for the JOIN.

But we can do even better by combining the joins from the two queries at the same level. Exactly what that looks like depends on the columns you need, how things will group, and how much is glossed over when adapting the real problem to a Stack Overflow question. The end result is something like this:

with Total as (
    select xyx... 
    from table a 
    join table b on b.account_key = a.account_key
    outer apply fngetaccount(a.account_key) acnt
    -- new join below for the table used in the CTE
    inner join tables abc on abc.account_value = acnt.account_value
    where b.period_end_dt >= '20170101'
    group by acnt.identifier_value 
)