Delete C

From TableC As C

Inner Join TableA As A

On (C.AID = A.AID)

Where A.fkey1ID = 'Your fkey1 Filter'

And Not Exists (

Select 1

From TableB...

OK, more correctly re-stating #3:

3. LEFT JOIN Table2 and check NULL on 1 of the non-nullable columns in Table2

It wasn't clear that the column being joined on allows Nulls.

>>but then the filter "D2.CORPOID IS NULL AND D.CORPOID IS NOT NULL" negates the join! The query will never return any rows!

Nope, that's not true in this case.

The check on...

You were using the LEFT JOIN to the derived table to test for existence of at least 1 record for a COID.

Try using NOT EXISTS ?

SELECT

...

It looks like SessionID is some sort of GUID-like value ?

These are usually poor candidates for being in the clustered index, unless they are always generated in an ascending order.

If...

Alternatively:

Create Table #A (ID int)

Insert Into #A Values (51)

Insert Into #A Values (78)

Insert Into #A Values (34)

Create Table #B (ID int, PID int)

Insert Into #B Values(20,4)

Insert Into #B Values(51,4)

Insert Into...

Yep, a CLR stored proc, with access to all the .Net System.IO namespace classes would do the trick nicely.

Of course, any organisation that has xp_cmdshell locked down, will likely also...

You could use sp_OAcreate and related stored procs to create an instance of the Scripting.FileSystemObject COM component and call the FileExists() and DeleteFile() methods.

Are the query plans the same in both cases. I suspect they are different, due to the change in behaviour when you remove the single quotes.

When you remove the quotes,...

See BOL under heading "Pattern Matching in Search Conditions"

Enclose the wildcard character in square brackets:

Where Column LIKE '%[_]%'

>>WHEN (Salary between 30000 and 40000) THEN Salary + 50000

Wow, that's a greater than 100% salary hike. Where is this place, I want to apply for a job 😉

For SSIS to go into a table to get configuration data, it needs to connect to the server hosting that table. That means you need at least a small subset...

Have you tried adding SET NOCOUNT ON at the start of the procedure ?

Some client apps like Crystal get confused by the SQL status messages returning the number of rows...

The command is

DBCC Log('YourDatabaseName', 1)

(2nd parameter is level of detail).

Run:

Backup Log YourDatabaseName With truncate_only

Checkpoint

The DBCC results should show only 2 entries

Now run your insert statement. It shouldn't take 10 million...

Are there indexes on the table being inserted into ?

Are there triggers on the table being inserted into ?

Are there any indexed (materialised) views that reference the table being inserted...