How can I insert the result of stored sp_tables_ex  into Table Temp

Posted on 2006-05-09
Medium Priority
Last Modified: 2008-01-09
I have a linked server called NW. The linked sever is used to access data from  Northwind.mdb I used exec sp_tables_ex 'NW' command to get table informations of Northwind.mdb . How can I get the result of the stored sp_tables_ex 'NW'  into table temp?Please help me
Question by:jujin
LVL 17

Accepted Solution

HuyBD earned 300 total points
ID: 16646261
Suppose you have created a temp table

insert into #tmp exec sp_tables_ex 'NW'

Assisted Solution

csachdeva earned 300 total points
ID: 16657710
The INSERT command supports calling a stored procedure to supply rows for insertion into a table.

CREATE TABLE #locks (spid int, dbid int, objid int, objectname sysname NULL,
 indid int, type char(4), resource char(15), mode char(10), status char(6))
INSERT #locks (spid, dbid, objid, indid, type, resource, mode, status)
EXEC dbo.sp_lock
SELECT * FROM #locks

This is a handy way of trapping the output of a stored procedure in a table so that you can manipulate it or retain it for later use. Prior to the advent of cursor OUTPUT parameters, this was the only way to perform further work on a stored procedure's result set within Transact-SQL.

INSERT...EXEC works with extended procedures that return result sets as well.

CREATE TABLE #cmd_result (output varchar(8000))
INSERT #cmd_result
EXEC master.dbo.xp_cmdshell 'TYPE C:\BOOT.INI'
SELECT * FROM #cmd_result
DROP TABLE #cmd_result

Hope that will answer your query.

Chetan Sachdeva

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question