SQL Server How to fetch data without loop for my scenario

T.Zacks 3,986 Reputation points
2021-08-13T09:03:37.883+00:00

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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2021-08-13T22:01:43.987+00:00

    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful