SQL How to optimize insert to table from temporary table

Question

I created procedure where dynamically collecting from various projects (Databases) some records into temporary table and from that temporary table I am inserting into table. With WHERE statement , but unfortunately when I checked with Execution plan I find out, that this query part take a lot of load. How can I optimize this INSERT part or WHERE statement ?

INSERT INTO dbo.PROJECTS_TESTS ( PROJECTID, ANOTHERTID, DOMAINID, is_test)
    SELECT * FROM #temp_Test AS tC
    WHERE NOT EXISTS (SELECT TOP 1 1 
                        FROM dbo.PROJECTS_TESTS AS ps WITH (NOLOCK)
                        WHERE ps.PROJECTID = tC.projectId 
                        AND ps.ANOTHERTID = tC.anotherLink 
                        AND ps.DOMAINID = tC.DOMAINID 
                        AND ps.is_test = tC.test_project 
                        )

Answer 1

I think you'd be better served by doing a JOIN than EXISTS. Depending on the cardinality of your join condition (currently in your WHERE) you might need DISTINCT in there too.

INSERT INTO dbo.PROJECTS_TESTS ( PROJECTID, ANOTHERTID, DOMAINID, is_test)
    SELECT <maybe distinct> tC.* FROM #temp_Test AS tC
    LEFT OUTER JOIN  FROM dbo.PROJECTS_TESTS AS ps on
                        ps.PROJECTID = tC.projectId 
                        AND ps.ANOTHERTID = tC.anotherLink 
                        AND ps.DOMAINID = tC.DOMAINID 
                        AND ps.is_test = tC.test_project 

where ps.PROJECT ID IS NULL

or something like that