I am generating about 500 records.

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

• The server is located elsewhere and would rather send one big pay load over.
• This seems like less overhead than sending 500 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 insert the rest in there and record that failure.

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