Retrieve a specific column from a stored process result set into a temporary table

Question

I have a stored procedure that returns a result set with approximately 20 columns. I'm only interested in extracting all the rows of a specific column (username).

Here is what I have:

--create temp table CREATE TABLE #USERNAMES( username char(10) ) --store results in a temp table INSERT INTO #USERNAMES exec dbo.getAccountInfo @subbed = 1 

This will not work because it wants to save the entire result set in the temp table, but the temp table has not defined all the necessary columns. How to change insert to insert username column from getAccountInfo result getAccountInfo into temp table #USERNAMES ?

Answer 1

If you are creating a loop-bound server (see my answer here: Get a column definition for the result set of stored procedures ), you can use OPENQUERY, for example

 INSERT #USERNAMES(username) SELECT username FROM OPENQUERY(loopback, 'EXEC dbname.dbo.getAccountInfo @subbed = 1;'); 

NOTE: the name db is important here!

Of course, you can always replicate a stored procedure or provide it with an optional argument that determines the form of the result set.