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)

Answer 2

You can try the approach:

select from mytable where id in (select id from othertable)

or left join:

select from othertable left join mytable using id

not sure what has better performance, also second query could give you empty rows if it is not declared as foreign key.

fly-by-post, feel free to improve it.