Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are you trying to take a list of tickets and assign them to an invoice, or is there more to it than that? –  ConcernedOfTunbridgeWells Dec 30 '11 at 18:17

1 Answer 1

If you're just trying to do a batched update to attach a bunch of tickets to an invoice (i.e. Invoices and tickets live in a 1:M relationship) then you have to get a list of ticket IDs into the sproc in the first place.

You could use a table variable as an input parameter if you have a version of SQL Server that supports these. Otherwise, you would have to encode the list of ticket IDs in some way, for example a list serialised as a string. You could encode the list as XML if you don't mind eternal damnation :)

If you're trying to add an Invoice record to a batch of tickets (i.e tickets live in a 1:0-1 relationship with invoices) then you have the same options for input.

In both cases the most efficient way to do the updates is likely to be creating a table variable with your list of invoice changes and then doing an update/insert operation joining the table variable against the main tables.

OTOH I can't think of anything more elegant than that.

If your problem is passing the list in across from the client via then you can pass in table-valued parameters in recent versions of ADO.NET. In older versions you are basically in the job of scrubbing and escaping the identifiers client side and passing the list across serialised as a string or XML (supported from .NET 2.0 and SQL Server 2005 onwards).

If you're using anything else (e.g. PHP) then you're probably going to have to drop down to the lowest common denominator - scrub the list of tickets and encode them as a string. Maybe the sproc can do some validation, such as checking the tickets all belong to the correct user.

share|improve this answer
    
The only thing I'd add to this is that if you are dealing with a large number of updates at a time, you "may" get more performance from writing the data into a temp table, generating an index on your joining key and then doing your updates. YMMV. Recommend testing different approaches if you need / want better performance. –  Brandon Dec 30 '11 at 23:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.