Is it possible to do something like this in SQL Server:
INSERT INTO MyTable (Col1,Col2,Col3,Col4,Col5,Col6,Col7) VALUES
SELECT Col1 FROM Func1(),
SELECT Col2 FROM Func2(),
SELECT Col3,Col4,Col5 FROM Func3(),
SELECT Col6 FROM Func4(),
SELECT Col7 FROM Func5()
I have a large number of functions which return one-value results and one function which returns 3 columns. I would like to insert all of this data into one row of a table?
I can see the function returning muliple columns as possibly being a problem?
If all functions return just one row...
INSERT INTO
MyTable (Col1,Col2,Col3,Col4,Col5,Col6,Col7)
SELECT
f1.col1, f2.col2, f3.col3, f3.col4, f3.col5, f4.col6, f5.col7
FROM
(SELECT Col1 FROM Func1()) AS f1
CROSS JOIN
(SELECT Col2 FROM Func2()) AS f2
CROSS JOIN
(SELECT Col3,Col4,Col5 FROM Func3()) AS f3
CROSS JOIN
(SELECT Col6 FROM Func4()) AS f4
CROSS JOIN
(SELECT Col7 FROM Func5()) AS f5
If the functions return more than one row, you need to join them in the normal way; with predicates that determine which left row gets joined to which right row.
INSERT INTO MyTable (Col1,Col2,Col3,Col4,Col5,Col6,Col7) VALUES
SELECT Col1 FROM Func1(),
SELECT Col2 FROM Func2(),
SELECT Col3 FROM (SELECT Col3,Col4,Col5 FROM Func3()),
SELECT Col4 FROM (SELECT Col3,Col4,Col5 FROM Func3()),
SELECT Col5 FROM (SELECT Col3,Col4,Col5 FROM Func3())
SELECT Col6 FROM Func4(),
SELECT Col7 FROM Func5()
You have to remove Values and all ","and brackets around each select statement.
