How to insert SP results in a multiple tables

I have an sp it results in 3 sets. Now the problem is i want insert these 3 result sets in to 3 temp tables. In these three results are different structures and no.of columns also different in these results.

SQL Server has no built-in mechanism for capturing multiple result sets returned from Stored Procedures where the result sets have differing numbers of columns and/or datatypes in the same column position. INSERT...EXEC can output multiple result sets, but they need to have the same number of columns with the same datatypes in each position. And both OPENQUERY and OPENROWSET will only return the first result set, no matter how many are returned by the stored procedure (or query).

However, this can be accomplished using SQLCLR. So, if you can't update the Stored Procedure, then you can do something like the following in .NET / C#:

using (SqlConnection _Connection = new SqlConnection(_ConnectionString))
{
  using (SqlCommand _Command = _Connection.CreateCommand())
  {
    _Command.CommandType = CommandType.StoredProcedure;
    _Command.CommandText = "SchemaName.StoredProcedureName";
    // parameters, if any

    _Connection.Open();

    using (SqlDataReader _Reader = _Command.ExecuteReader())
    {
      // Stored Procedure has been executed. Now save the results.
      // Using the Context Connection allows access to local temp tables.

      using (SqlConnection _Connection2 = new SqlConnection("Context Connection = true;"))
      {
        using (SqlCommand _Command2 = _Connection2.CreateCommand())
        {
          _Connection2.Open();

          _Command2.CommandText = "INSERT INTO #Temp1 (column_list)
                 VALUES (@Param1, @Param2, ...);";

          SqlParameter _Param1 = new SqlParameter("Param1", SqlDbType.Int);
          _Command2.Parameters.Add(_Param1);

          // define addition parameters

          while (_Reader.Read())
          {
            _Param1.Value = _Reader.GetValue(0);
            _Param2.Value = _Reader.GetValue(1);
            // set additional parameters

            _Command2.ExecuteNonQuery();
          }

          _Reader.NextResult(); // move to result set #2

          _Command2.Parameters.Clear(); // reset input parameters
          _Command2.CommandText = "INSERT INTO #Temp2 (column_list2)
                 VALUES (@Param1, @Param2, ...);";

          SqlParameter _Param1 = new SqlParameter("Param1", SqlDbType.Int);
          _Command2.Parameters.Add(_Param1);

          // define addition parameters

          while (_Reader.Read())
          {
            _Param1.Value = _Reader.GetValue(0);
            _Param2.Value = _Reader.GetValue(1);
            // set additional parameters

            _Command2.ExecuteNonQuery();
          }

          _Reader.NextResult(); // move to result set #3

          _Command2.Parameters.Clear(); // reset input parameters
          _Command2.CommandText = "INSERT INTO #Temp3 (column_list3)
                 VALUES (@Param1, @Param2, ...);";

          SqlParameter _Param1 = new SqlParameter("Param1", SqlDbType.Int);
          _Command2.Parameters.Add(_Param1);

          // define addition parameters

          while (_Reader.Read())
          {
            _Param1.Value = _Reader.GetValue(0);
            _Param2.Value = _Reader.GetValue(1);
            // set additional parameters

            _Command2.ExecuteNonQuery();
          }
      }
    }
  }
}

Now, since you want to store the results into local temporary tables, those need to be created before you execute this SQLCLR stored procedure. If you create them inside the SQLCLR stored procedure, they won't exist once the stored procedure completes.

CREATE TABLE #Temp1 (column1 DATATYPE_1, column2 DATATYPE_2, ...);
CREATE TABLE #Temp2 (column1 DATATYPE_1, column2 DATATYPE_2, ...);
CREATE TABLE #Temp3 (column1 DATATYPE_1, column2 DATATYPE_2, ...);

EXEC dbo.SQLCLR_StoredProcedure;

SELECT * FROM #Temp1;
SELECT * FROM #Temp2;
SELECT * FROM #Temp3;


BUT, as @AndriyM pointed out in a comment on this answer, if you are able to modify the Stored Procedure, then given that the local temporary tables need to be created first anyway**, it would be better / simpler to modify your Stored Procedure to insert directly into the temp tables, as opposed to adding logic to control which single result set gets returned. In this case, you can detect the existence of the local temp tables and if they do exist, then insert into them, else return the result sets as the Stored Procedure currently does, and this will ensure that any existing code that calls this Stored Procedure will continue to get the three expected result sets. And you don't even need to add any optional input parameters. Just use the following approach per each of the three queries that returns a result set:

`IF (OBJECT_ID(N'tempdb..#Temp1') IS NULL)
BEGIN -- Temp Table does NOT exist, so return result set
SELECT col_1, col_2, col_3, ...
FROM SchemaName.TableName
WHERE ...
END;
ELSE
BEGIN -- Temp Table DOES exist, so insert results into it
INSERT INTO #Temp1 (column1, column2, column3, ...)
SELECT col_1, col_2, col_3, ...
FROM SchemaName.TableName
WHERE ...
END;

StackExchange Database Administrators Q#155978, answer score: 3