All geek questions in one place

Insert multiple select statements in a table as values

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 that return unambiguous results and one function that returns 3 columns. I would like to insert all this data in one row of the table?

I see a function returning multiple columns, possibly being a problem?

+4
source share
3 answers

If all functions return only one line ...

 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 functions return more than one line, you need to join them in the usual way; with predicates that determine which left row joins which right row.

+10
source 
  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()
0
source

You need to remove Values and all "," and the brackets around each select statement.

0
source

Source: https://habr.com/ru/post/1415573/

More articles:


All Articles