SQL Server How to fetch data without loop for my scenario

Question

See my code where i iterate data in temp table and search records. if found then insert that records into another temp table. if not found then also insert that records into temp table as NOTFOUND data for ModelFile field.

without loop how can i do this operation. please guide me.

    SELECT @RowCount=COUNT(*) FROM #TempCurrentModel          

    WHILE @Row <= @RowCount                
    BEGIN             
        SELECT @ModelName=ModelFile,@TickerName=Ticker,@ClientName=Client FROM #TempCurrentModel WHERE AutoID=@Row          

        IF(CHARINDEX('.', @ModelName) > 0)
        BEGIN
            SELECT @ModelName = SUBSTRING(@ModelName, 1, CHARINDEX('.', @ModelName)-1)+'.xls'
        END

    IF EXISTS(          
        SELECT TOP 1 ID,ModelFile,DownloadDate,Ticker,Client from tblOriginalModelInput                   
        WHERE  Convert(varchar(10),downloaddate,112)<convert(varchar(10),GetDate(),121)            
        AND ModelFile LIKE @ModelName+'%'       
        AND Ticker IN (select Ticker from tblTicker where Active='A' AND Published='Y')          
        ORDER BY downloaddate DESC            
    )          
    BEGIN          
        INSERT INTO #TempPreviousModel(ID,ModelFile,DownloadDate,Ticker,Client)          
        SELECT TOP 1 ID,ModelFile,DownloadDate,Ticker,Client from tblOriginalModelInput                   
        WHERE  Convert(varchar(10),downloaddate,112)<convert(varchar(10),GetDate(),121)            
        AND ModelFile LIKE @ModelName+'%'       
        AND Ticker IN (select Ticker from tblTicker where Active='A' AND Published='Y')          
        ORDER BY downloaddate DESC          
    END          
    ELSE          
    BEGIN          
        INSERT INTO #TempPreviousModel(ID,ModelFile,DownloadDate,Ticker,Client)          
        SELECT 0,'NOTFOUND '+@ModelName,GetDATE(),@TickerName,@ClientName          
    END          
        SET @Row=@Row+1          
    END

Answer 1

Maybe this:

INSERT INTO #TempPreviousModel(ID,ModelFile,DownloadDate,Ticker,Client)          
   SELECT isnull(MI.ID, 0), isnull(MI.ModelFile, 'NOTFOUND-' + cm.ModelName), 
          isnull(MI.DownloadDate, sysdatetime()), isnull(MI.Ticker, cm.Ticker), 
          isnull(MI.Client, cm.Client)
   FROM   #tempCurrentModel CM
   OUTER  APPLY (SELECT TOP 1 M.ID, MI.ModelFile, MI.DownloadDate, MI.Ticker,Client 
                 FROM   tblOriginalModelInput MI
                 WHERE  convert(date, MI.downloaddate) < convert(date ,GetDate())            
                   AND  MI.ModelFile LIKE cm.ModelName+'%'       
                   AND  MI.Ticker IN (select T.Ticker from tblTicker T WHERE Active='A' AND Published='Y')          
                 ORDER  BY MI.downloaddate DESC) AS MI

Note that since you did not provide table definitions or test data, I have not been able to test this.

By the way, I took the liberty to change this condition:

WHERE  Convert(varchar(10),downloaddate,112) < convert(varchar(10),GetDate(),121)

As looks very funny. Format 112 is YYYYMMDD, while 121 is YYYY-MM-DD, so that comparison could yield unexpected results.