Inserting multiple select statements into 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 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?

  • Connecting to MSSQL with Codeigniter PHP
  • Unable to set a condition in SQL Server 2008 R2
  • Table Valued Constructor Maximum rows limit in Select
  • Import / Export database data with SQL Server Management Studio
  • No indexes on small tables?
  • How do I check the existence of a table using a variable as tablename
  • I can see the function returning muliple columns as possibly being a problem?

    3 Solutions collect form web for “Inserting multiple select statements into a table as values”

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.