I am generating about 500 records.

What I want to do is INSERT them all at the same time because:

• The server is located elsewhere and I would rather send one big payload over.
• This seems like less overhead than sending 500 separate insert queries over the wire.

But because they're batch inserting, I don't want to rollback everything if one insert fails - I want to try to insert the rest in there and record the failure(s).

What's the best way to do this in SQL Server? Is BULK INSERT what I want?