I have two tables, a large (millions) table of
Tickets for orders, and a small (hundreds of thousands) table for
Invoices metadata on a subset of those tickets. The metadata is information about when a ticket was paid and is only created when a ticket is first paid.
I'm currently using a view to present all of this data; a
LEFT OUTER JOIN shows all rows from
Tickets and associated
Invoices data if it's there.
My problem is how do I handle creating new
Invoices metadata? I can't update the view directly for new entries, so I have a stored procedure that takes a ticket number and creates or updates the
Invoices table, however it only does one ticket at a time; I could be updating hundreds of tickets, so this doesn't work.
There's also no simple select criteria I can use; a specific set of potentially non-sequential tickets may be entered. I can't input a range of tickets, I need an explicit
WHERE ticketno IN (Ticket#1, Ticket#2) sort of statement to update only the correct rows.
What is the best way to this sort of insert for related data? I've seen some implimentations of stored procedures taking in a list of values as a parameter, but all of them look very hacky and risky.