In theory, increased demand for SPY can push its market price above the value of the stocks it holds, but it normally cannot stay far above that value because of the ETF creation/redemption mechanism.
SPY's underlying value, or NAV (net asset value), is based on the value of the securities in the fund. Suppose the underlying portfolio is worth $300 per SPY share, but intense buying pushes SPY's market price to $310. That creates an arbitrage opportunity.
Authorized participants can effectively acquire the appropriate basket of underlying securities, exchange that basket for newly created SPY shares, and sell those ETF shares at the higher market price. This increases the supply of SPY and tends to push its price back toward its NAV.
The reverse happens if SPY becomes substantially cheaper than its underlying holdings. Arbitrageurs have an incentive to buy the discounted ETF and use the redemption mechanism, helping bring the ETF price back toward NAV.
Therefore, SPY could temporarily trade at a premium or discount, particularly during unusual market conditions, but it could not ordinarily “go to the moon” solely because everyone wanted SPY while the S&P 500 companies simultaneously collapsed.
The key distinction is:
Individual stock: supply and demand directly determine its market price.
ETF: supply and demand affect its market price too, but creation/redemption arbitrage generally keeps that price closely connected to the value of its underlying assets.
So if the S&P 500 stocks collectively fall substantially, SPY should also fall substantially, despite strong demand specifically for SPY. The ETF structure is designed to keep the two values closely aligned.