There appears to be two motives. The first is the desperate thirst of Collins and his fellow soldiers. Note what Collins says to his companions that sets the story in motion:
Collins of A Company said: "I wisht I had a drink. I bet there's water in that there ol' well yonder!"
Thus we can see that the main reason that causes Collins to risk his life so spectacularly is his tremendous thirst. However, a second reason might also be the way that he is challenged by his companions and made fun of because of his thirst:
Some comrades joked Collins about his thirst. "Well, if yeh want a drink so bad, why don't yeh go git it?"
Collins, in response, says, "Well, I will in a minnet if yeh don't shut up." Clearly, then, we can see that his decision to go and get some water is partly a response to being made fun of and a desire to shut his comrades up. The twin motives of personal need and desire and then also not wanting to be made fun of are the two factors that therefore lead Collins to make his incredible heroic dash for water.
One motive that may belong to Collins in his desperate act of running for the water, is his frustration at his company's futility in their battle with their enemy, for like the horses that pull the cannons, Collins and his fellow "brute-soldiers" are at this point "but passive and dumb spectators," too. Overriding this is, perhaps, his resentment against his comrades and as he feels that he must accept the dare made to his manhood by his comrades, "Dern yeh! I ain't afraid t'go. If yeh say much, I will go!" For, when he seeks the officers in order to ask permission to fetch the water, the colonel and the captain are nonplussed about the soldier, wondering whether he truly wants to go or not.
As he prepares to go, Collins is again compared to a horse; the men examine him as grooms would before a horse race. Moreover, it is at this point that the naturalism of Crane's writing is fully apparent as he describes the movement of the "compact column" of the soldiers, an "animal-like thing" that only slightly moves. "Its four hundred eyes were turned upon the figure of Collins."
When Collins does race against fate and fetch the water, he oddly stops on his return because the wounded artillery officer asks for a drink. As he has realized "heroes were not much," Collins risks his life for the wounded officer, giving him water. When he returns, he cheers, but the indifferent force of nature has allowed the bucket to have been shot, so that it is now empty, and all the efforts of Collin have been merely futile.