I have a job which each day (at 6:01pm) copies 516 rows from a table and seeds them with tomorrow's date
INSERT MYDB.MARKET_DATA( MARKET_DATE, CURRENCY_BASE, CURRENCY_TERM, PERIOD, MID) ( SELECT DATEADD(hh, 6, GETDATE()), MARKET_DATA.CURRENCY_BASE, MARKET_DATA.CURRENCY_TERM, MARKET_DATA.PERIOD, MARKET_DATA.MID FROM MYDB.MARKET_DATA with (nolock) WHERE cast(MARKET_DATE as date)=cast (getDate() as date) )
We also check that the right number of rows are copied by looking at the number of rows for the last 7 days
SELECT count(*) ,CAST(market_date AS DATE) FROM mydb.market_data WHERE market_date > dateadd(day, - 7, getdate()) GROUP BY CAST(market_date AS DATE) ORDER BY CAST(market_date AS DATE) DESC
We ran this SELECT query twice today. At 10am in the morning it returned 517 rows for today, and at 1pm it returns 516 rows for today.
What could possibly account for this discrepancy?
- There was no user interaction with this table today (other than running the two SELECT's)
- There is no
with (nolock)on the SELECT query
- Note that there is a
with (nolock)on the INSERT/SELECT, because the rows are updated throughout the day with latest information, and I don't want the INSERT/SELECT to fail on any rows that are being updated at the precise moment that it is being executed
- There is no stored procedure or job to delete any rows