OP, your post makes me so sad. It simply cannot be right to make an 11 year old child feel like a failure, but that's exactly what we've done to a significant proportion of children across the country, thanks to these stupid bloody tests.
My dd did the KS2 SATS this year, but she is one of the lucky ones who finds jumping through hoops for academic tests relatively easy. Had she been tested on art or aspects of PE, it would have been a very different story. Children have so many different talents, and they are all valuable. Are there things you can do this summer that remind your DS of his strengths?
What the children learn and achieve at primary school cannot be summed up by a few stupid numbers based on a couple of tests at the end of the year. It isn't fair on the teachers or the children. This year, it was particularly unfair as the year 6 cohort had not had long enough to cover the new curriculum properly.
My dd's teacher told the class that she didn't care what results they got as long as they did their best, and as far as she was concerned, it was far more important that they were nice people who were kind and supportive to one another. Is that a message you can emphasise to your DS? My dd is quite academic but I have always told her that it's the type of person she is that matters, much more than academic results.
I don't think your DS will have to re-sit in year 7 as that doesn't come in until next year, but I agree with the suggestions that it might be worth meeting the SENCO at the new school to discuss any support needs. Could you let the new school know that the SATS have knocked his confidence a bit, so that they can perhaps try to build him up a bit?