You can use OPENROWSET. In your case, it would be like this:SELECT * INTO #temp FROM OPENROWSET( 'SQLNCLI', 'Server=(local);Trusted_Connection=yes', 'EXEC YourDatabaseNameHere.dbo.test')
If not already enabled, you will need to enable Ad Hoc Distributed Queries for this to work. To do this:sp_configure 'Show Advanced Options', 1GORECONFIGUREGOsp_configure 'Ad Hoc Distributed Queries', 1GORECONFIGUREGO
I don't really use this - it is sort of a workaround for a problem that should be avoided, in my opinion. But it is there if you want to use it.