The statement said the investigation should be "prompt, impartial, credible and transparent".
It also condemned the "acts" which led to the deaths of at least 10 civilian activists during the operation.
The raid sparked strong international condemnation and calls for Israel to lift its three-year blockade of Gaza.
The UN statement was reached after hours of discussion as the council deliberated through the night.
In Turkey, Prime Minister Recep Tayyip Erdogan called Israel's raid a "bloody massacre" as he addressed parliament.
Egyptian President Hosni Mubarak has ordered the border crossing between Egypt and the Gaza Strip to be opened.
The Egyptian state news agency said it was to allow humanitarian aid through. It is not clear how long the crossing at Rafah will be kept open.
The UN statement was the result of a compromise between Turkey and the United States, with Turkey reluctant to water down its trenchant criticism of Israel while the United States, Israel's closest ally, wanted to temper the language used, says the BBC's UN correspondent Barbara Plett in New York.
Turkey is furious at the commando raid, which targeted a Turkish ship and appeared to have killed mostly Turkish activists, our correspondent says.
The compromise took out direct condemnation of Israel and removed references to an international investigation, our correspondent adds.
It also weakened demands for an end to the economic blockade of Gaza that the activists were trying to break, but the incident has refocused international attention on the siege and many states have renewed calls for it to be lifted, she says.
The Palestinian Observer at the UN, Riyad Mansour, said he was disappointed that the language in the final draft of the statement had been softened.
On the request to end the Gaza blockade, Mr Mansour said this was "perhaps the clearest statement by the Security Council requesting and demanding lifting the siege of the Gaza Strip".
In its statement, the Security Council said it "deeply regretted the loss of life and injuries resulting from the use of force during the Israeli military operation in international waters against the convoy sailing to Gaza".
The Council "condemns those acts which resulted in the loss of at least 10 civilians and many wounded, and expresses its condolences to their families".
The Council requested the immediate release of the ships as well as the civilians held by Israel.
It also stressed that the situation in Gaza was "not sustainable".
Shortly after the UN statement was released, several Gaza militants crossed the border into Israel and exchanged fire with troops, the Israeli military said. Two militants were killed, it said.
Russia and the European Union have also jointly called for an impartial inquiry into the Israeli operation and for crossings into Gaza to be opened.
Earlier, Turkey's foreign minister called Israel's actions "murder by a state".
Israel's UN envoy said troops acted in self-defence when activists attacked them, charges the campaigners deny.
"This flotilla was anything but a humanitarian mission," Israel's deputy UN ambassador Daniel Carmon said.
He said the activists had used "knives, clubs and other weapons" to attack the soldiers who boarded the lead boat, the Mavi Marmara.
Captain Aria Shaliker of the Israel Defense Forces, who was part of the operation on Monday, says the commandos began the raids armed with paint ball guns.
"I was, myself, on one of the boats, the Israeli boats, approaching the flotilla," he told the BBC's World Today programme.
"It is true that the Israeli commander unit... came on board with paint ball weapons... in order to disperse [people] if there was violence. They were ready for a violent... demonstration on board the flotilla, especially on the big boat, the Marmara. No-one really expected that there would be such a violent outcome of what happened.
"First, you know, the soldiers tried to disperse, but in the end when they were shot at, you know when there was shooting... from the other side, there's no other way than turning from paint ball to live ammunition."
The campaigners say the soldiers opened fire without any provocation.
Of the 679 surviving activists, who were brought to the Israeli port of Ashdod, only 50 agreed to be voluntarily deported and more than 30 are being treated in hospital for their injuries, reports the BBC's Wyre Davies in Jerusalem.
That means that almost 600 people, from several countries, are still being held in detention centres across Israel and are being questioned by the authorities.
Israel has imposed an information blackout, making it difficult to gather first-hand accounts from the campaigners.
The ships were carrying 10,000 tonnes of aid in an attempt to break Israel's three-year blockade of the Gaza Strip.
Israel says it will deliver the ships' aid cargo to Gaza by land.
Later on Tuesday, Nato ambassadors will hold emergency talks at Turkey's request to discuss the raid.
Israeli Prime Minister Benjamin Netanyahu said he regretted any loss of life, but gave full backing to the action of the Israeli troops.
Mr Netanyahu cut short a visit to Canada to deal with the growing crisis and cancelled a scheduled meeting in Washington with US President Barack Obama on Tuesday. He is due back in Israel on Tuesday.
Israel tightened its blockade of the Gaza Strip after Hamas seized power there in 2007.
Israel says it allows about 15,000 tonnes of humanitarian aid into Gaza every week, but the UN says this is less than a quarter of what is needed.