Set Based Query

[Leighton21]

Hi All,

I am trying to transfer data from one database table (ODBC linked table non SQL Server) to a SQL table. I have this working and I use a configuration table to determine what data needs to be transferred. The config table is simply

[Item Name], [Frequency], [Date Last Transferred] where a SQL job runs and checks the current time against the Frequency and Date Last Transferred for an Item and if it is greater than the calc send the data. Currently the config table only has 10 items. I have used a looping structure (basically a cursor) to go through the config table an select any data from the linked table that is new (greater than the last modified date).

so in the loop I would have something like

select [item name], [value date], [value] from table1 where [item name] = 'XXXX'and [value date] > than @datelastmodified

the problem is the larger the config table becomes the slower the loop/cursor wil become.

So my question would be is if anyone knows of a way to use a set based approach that could grab all of the items at once bare in mind the frequency for an item could be 1 hour,2 hours 1 day etc and the last modified will never be the same as another item

 

[PWise]

have a standard for Frequency
let say minutes

60 for 1 hour
120 for 2 hours
1440 for 1 day
2880 for 2 days
......

select [item name], [value date], [value]
from table1
inner join config
on [item name] =[item name]
an dateadd(n,Frequency,[Date Last Transferred]>getdate()

 

[Leighton21]

Thanks pwise your a legend