question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked ErlandSommarskog answered

SQL Server How to fetch data without loop for my scenario

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.