Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inserting multiple select statements into a table as values

Tags:

sql

sql-server

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?

like image 416
mezamorphic Avatar asked Jun 01 '12 12:06

mezamorphic


People also ask

How do I insert multiple records in a table?

INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.


1 Answers

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.

like image 176
MatBailie Avatar answered Nov 15 '22 06:11

MatBailie