alternatives to using IN clause

Question

I am running the below query:

SELECT 
    ReceiptVoucherId, 
    VoucherId, 
    ReceiptId,
    rvtransactionAmount, 
    AmountUsed, 
    TransactionTypeId
FROM 
    [Scratch].[dbo].[LoyaltyVoucherTransactionDetails]
WHERE       
    VoucherId IN 
    (2000723,
    2000738,
    2000774,
    2000873,
    2000888,
    2000924,
    2001023,
    2001038,
    2001074,
    2001173)

the aim being to extract the ReceiptVoucherId / VoucherId / ReceiptId / rvtransactionAmount / AmountUsed / TransactionTypeId data for the list of voucherId's that I have.

My problem here is that my list of VoucherID's is 187k long so an IN clause is not possible as it returns the error:

Internal error: An expression services limit has been reached

Can anyone advise on a alternative to doing it this way?

I am using SSMS 2014

Answer 1

Just create a table containing all this Vouchers (Hopefully you already have one) and then use IN() selecting from the table :

SELECT 
    ReceiptVoucherId, 
    VoucherId, 
    ReceiptId,
    rvtransactionAmount, 
    AmountUsed, 
    TransactionTypeId
FROM 
    [Scratch].[dbo].[LoyaltyVoucherTransactionDetails]
WHERE       
    VoucherId IN (SELECT VoucherId FROM VourchersTable)