CODEWITHSUNDEEP
sqlsql-server-2008insertperformance
JustBeingHelpful
JustBeingHelpful

Reputation: 18980

Looking for most efficient way to write this INSERT query

Looking for most efficient way to write this INSERT query (and least code):

INSERT INTO [exportnote] (
    [versionnumber],
    [createdate],
    [LabAccessioningNumber],
    [Text],
    [exporttestresultreportid]
) 
SELECT 
    1,
    GETDATE(),
    @lan,
    notetemp.notetext,
    @new_exporttestresultreportid
FROM 
(
    SELECT dbo.[note].notetext
    FROM dbo.[note] 
    WHERE  
        [note].notetypeid IN (15,17) -- SAMPLE NOTES  possibly 5,3
        AND [note].noteobjectid = @sampleid -- dbo.[sample].sampleid
        AND [note].active = 1 

    UNION ALL

    SELECT 
        dbo.[note].notetext
    FROM 
        dbo.[note]
    WHERE 
        [note].notetypeid IN (11) -- CLIENT NOTES
        AND [note].noteobjectid = @clientid -- dbo.[client].clientid
        AND [note].active = 1

    UNION ALL

    SELECT 
        dbo.[note].notetext
    FROM 
        dbo.[note]
    WHERE 
        [note].notetypeid IN (2,7) -- TEST NOTES
        AND [note].noteobjectid = @testid -- dbo.[test].testid
        AND dbo.[note].active = 1
) AS notetemp

Upvotes: 1

Views: 96

Answers (2)

gbn
gbn

Reputation: 432311

This? OR and UNION on the same table are often interchangeable: 

INSERT ...
SELECT
    1,
    GETDATE(),
    @lan,
    n.notetext,
    @new_exporttestresultreportid
FROM dbo.[note] n
WHERE  
    n.active = 1 
    AND
    (
       (n.notetypeid IN (15,17) AND n.noteobjectid = @sampleid)
        OR
       (n.notetypeid IN (2,7) AND n.noteobjectid = @testid)
        OR
       (n.notetypeid IN (11) AND n.noteobjectid = @clientid)
    )

Upvotes: 3

Lost in Alabama
Lost in Alabama

Reputation: 1653

You could merge the 3 selects into 1:

SELECT dbo.[note].notetext    
 FROM dbo.[note]      
WHERE  [note].active = 1         
 AND  ( 
           ( [note].notetypeid IN (15,17)       
              AND [note].noteobjectid = @sampleid )
       OR (  [note].notetypeid IN (11) 
              AND [note].noteobjectid = @clientid )
       OR (  [note].notetypeid IN (2,7)        
             AND [note].noteobjectid = @testid )
      )

Upvotes: 2

Related Questions