I have a lot of VALUES I want to INSERT. But only a subset of them - only those that meet a JOIN criteria involving another table.
I could INSERT them into a temp table, and then do a SELECT INTO. But do I need to do that? Is there any way to do a INSERT... VALUES ... WHERE...
As per my reply to your "Bulk INSERT with individual failure" thread, for what you want to do a temporary staging table (or several) with minimal constraints is truly your best option to accomplish your goal.
Having a large amount of literals in an INSERT statement like discussed is a bad idea.
If you're loading from a file, just use some bulk load feature that bypasses the need for INSERT statements, into some temp file matching the format of the file.
If you're loading from a program, use a prepared single-row INSERT statement, bundling batches of say a few thousand rows at a time into a single transaction.
Then pretend your newly populated temp table was your original source, but now it is visible to SQL, and you can load your regular tables from the temp table(s) much more nicely.
