Re: INSERT... WHERE

On 2013.01.13 6:00 PM, Robert James wrote:
> 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.

-- Darren Duncan