I have an inline table-valued function. I want to insert the output of that function into a table variable so I can pass it to another function.
DECLARE @PeopleToEmail mb.PeopleToEmailOrPhone;
INSERT INTO
@PeopleToEmail
SELECT
*
FROM
mb.GetOptedInEmails('All')
;
On my dataset, this query takes 28 seconds to run.
However, if I use an intermediary temporary table, runtime drops to around 9 seconds.
SELECT
*
INTO
#OptIns
FROM
mb.GetOptedInEmails('All')
;
DECLARE @PeopleToEmail mb.PeopleToEmailOrPhone;
INSERT INTO
@PeopleToEmail
SELECT
*
FROM
#OptIns
This makes no sense to me and means my code is twice as long as it needs to be. Can anyone help me
- Understand why this is happening.
- Think of another way of optimising the query without the intermediary table.
I'm stuck on SQL Server 2012. The function is quite long but you can view it here.

