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