We have this shallow well pitcher pump, but the description at the website says it will pump up to 26 feet. (We are in Middle Georgia, very sandy soil, and we found our water at 5 feet. We dug down another 9 feet for good measure.)
If you are going to drill by hand, you have to be able to pull the weight of 200ft of the pipe being used as the drill.
The cost of the pipe may also be very high.
It may be best to have a well borer come in and do it.
Can you catch rainfall instead?
Hand pumps working on vacuum can only lift about 25 feet, the water vaporises at that point.
If you have buckets in the pump, you then have the weight of the buckets and the water for the fall 200ft depth.
John Daley Bendigo, Australia
The Enemy of progress is the hope of a perfect plan
Hand pumps are common in India and I know they are often more than 100 feet deep. At our school we had one drilled (by a big machine) that was 80 feet deep. We could easily pump water from it. Maybe 15 empty pumps of the handle up and down before water came, and then it came thick and fast.
Works at a residential alternative high school in the Himalayas SECMOL.org . "Back home" is Cape Cod, E Coast USA.