Insert results of a stored procedure into a temporary table

Versatility: More than one way to fill a temp table

OPENROWSET: The highway approach

Utilise OPENROWSET to directly insert data from your stored procedure, skipping any declaration of temp table structures:

-- Your DBA might cry, but let's switch on Ad Hoc Distributed Queries
EXEC sp_configure 'show advanced options', 1; -- Unleash the leviathan
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; -- Open the gates
RECONFIGURE;

INSERT INTO #TempTable
EXEC ('EXEC YourStoredProcedure') 
AT YourLinkedServer; -- Long live the king!


Remember: Ad Hoc Distributed Queries should be enabled. This method can have security implications, so handle with care, it's hotter than a spilt cup of coffee on your keyboard.

ITVFs: Elegance in simplicity

Not just a fancy acronyme: If your stored procedure can be converted into an ITVFs, you're able to utilise SELECT INTO, which is friendlier than a golden retriever:

-- ITVFs – making your life a little bit easier
SELECT * INTO #TempTable
FROM dbo.ChatWithYourITVF(params);


ITVFs: simple, efficient, cleans up after itself by offering implicit temp table creation.

Stored procedures within procedures: The nesting doll approach

Wrap one stored procedure in another or pair them with ITVFs for ultimate parameter management and a dose of upgradeability that future you will thank you for:

-- Hold my coffee, I'm going in...
CREATE PROCEDURE YourOuterStoredProcedure AS
BEGIN
   SELECT * FROM YourITVF();
END;

-- Data captured. Let's ride!
INSERT INTO #TempTable EXEC YourOuterStoredProcedure;


OPENQUERY: Not just for linked servers

While linked servers love OPENQUERY, it's also handy for indirect stored procedure execution. It creates temp tables without needing predefined schema:

-- YES! Linked servers can be friends too :)
EXEC sp_serveroption 'YourLinkedServer', 'DATA ACCESS', 'TRUE';

SELECT * INTO #TempTable
FROM OPENQUERY(YourLinkedServer, 'EXEC YourStoredProcedure'); -- Like a boss.


The SQL Barista's guide to perfect storage

Optimize insert performance: Like tuning your coffee grind

Use the right tools, in the right way:

• Relevant Indexes: Optimize performance for large temp tables.
• Batching: Insert data in batches to minimize log contention and peak memory usage.

Avoiding the spills: Common Pitfalls

Keep your workspace clean:

• Check permissions. You can't borrow someone else's coffee beans!
• Be careful with performance. Method over madness when it comes to large data sets.
• Triple-check your stored procedure for a single result set. The best baristas only serve a single espresso shot!