Weight is the factor here. Diesel weighs about 7.5 pounds per gallon, although it varies with the season. So 100 gallons weighs 750 pounds and five hundred gallons would weigh 3,750 pounds.
Next thing to figure is the size of the pickup. A one ton dually could probably handle 500 gallons, but if you put that in a half ton, you'd bust your suspension and axle.
There are limits as to where you need a tank endorsement on your license depending on the state. I think 500 gallons is fine, but I'm not sure.
The bigger the tank, the more permanent it becomes. Securing two tons isn't easy, especially when you factor in a slosh factor. (As you drive down the road, without baffles, fluids move. That shifting weight will influence your truck big time.)
Most of the transfer tanks I've seen are in the 100 or less gallon range. It doesn't take up too much room in the bed, can be easily mounted permanently, and don't break the bank too badly when you fill it up.
Best of luck,
Jim Clark-Dawe