Geeks With Blogs
Happy Daze! Notes from my world of (mis)adventures....
As one of my KPI’s I am responsible for implementing a solution to split one of my databases in two… no problem you may think except if I tell you that one table is 100GB’s big (don’t ask I am new here). Having limited experience working with such a cumbersome amount of data I am learning things as I go. So as a first port of call I have decided to create a series of holding tables that I am going to use to hold one months worth of data, drop the original table rename the holding table, implement the original constraints and hey presto step one complete … or so I thought. When it came to doing a cross check to ensure the number of records exported matched the number of records imported I wanted the script to be dynamic and used DATEADD(m, GETDATE(), -1) to ensure that I always retrieved a rolling month of data. This, it would turn out, would cause much frustration and cursing when my tables would not balance. The reason was due to the export taking so long that there was a significant difference in time between the getdate() in the retrieval script and the getdate() in the balancing script. The solution was to change the date calculation to be just that and not date and time which was causing the skew… solution below (I personally prefer ISO format): DATEADD(m, -1, CONVERT(VARCHAR(8), GETDATE(), 112))

Cheers

David

Posted on Friday, October 7, 2011 1:20 PM | Back to top


Comments on this post: Data Balancing GOTCHA!

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © OceanWanderer | Powered by: GeeksWithBlogs.net