Inserting into a user-defined table type via a dynamic query in SQL Server?-CodePudding

I have a user-defined table type tyAnalysisNumbers. I need to populate my user defined data type within a stored procedure with a SELECT statement and I am struggling to get that working within my stored procedure.

The following ways I have tried do not work

DECLARE @MyTable tyAnalysisNumbers;

INSERT INTO @MyTable 
    EXEC ('SELECT * FROM '   @someTable);

I get this error:

An INSERT EXEC statement cannot be nested

I am unsure how to insert into my custom table via a select statement.

Can anyone help me accomplish this?

CodePudding user response：

An INSERT EXEC statement cannot be nested

Above error is self explanatory. Please look at below scenario:

For example, we have one procedure which inserts data in table type and return result.

CREATE PROCEDURE uspInsertData1
AS 
BEGIN
    DECLARE @MyTable tyAnalysisNumbers;

    INSERT INTO @MyTable 
    EXEC ('SELECT * FROM someTable');

    select * from @MyTable
END

Now, lets say we have another procedure which will call above procedure and again insert data in another table.

CREATE PROCEDURE uspInsertData2
AS
BEGIN
    DECLARE @MyTable tyAnalysisNumbers;

    INSERT INTO sometable
    EXEC uspInsertData1
END

Now, if you execute 1st procedure it will work fine but if you execute second procedure you will get this error.

An INSERT EXEC statement cannot be nested.

Because now you have nested EXEC statements.

I hope you understand this. If not please share your stored procedure code.

CodePudding user response：

The challenge you're facing here is that you cannot directly insert the result of an EXEC into a table. So as an alternative try with an intermediate #temp

DECLARE @MyTable tyAnalysisNumbers;

exec ('SELECT * INTO ##Temp FROM '   @someTable);

INSERT INTO @MyTable 
SELECT * FROM ##TEMP