SP reults into a DB table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP reults into a DB table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-24 : 10:21:52

Rez writes "Is it possible to insert the resultset of a SP into an existing table? I dont believe it is. Ive tried the some code you had on a previous page:

CREATE TABLE #temp123(
col1 varchar (10),
col2 varchar (10),
col3 varchar (10),
col4 varchar (10))
INSERT INTO #temp123
exec s_SpaceUsed 're_live'
SELECT * FROM #temp123

However after running the first 7 lines, an error message appears:
An INSERT EXEC statement cannot be nested.

Please email me a response! Thanks!"

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-24 : 10:33:51

[code]

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO

DECLARE @tablename nvarchar(128)
, @maxtablename nvarchar(128)
, @cmd nvarchar(1000)
SELECT @tablename = ''
, @maxtablename = MAX(name)
FROM sysobjects
WHERE xtype='u'

WHILE @tablename < @maxtablename
BEGIN
SELECT @tablename = MIN(name)
FROM sysobjects
WHERE xtype='u' and name > @tablename

SET @cmd='exec sp_spaceused['+@tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @cmd
END

SET NOCOUNT OFF
GO

SELECT * FROM #SpaceUsed
GO

DROP TABLE #SpaceUSed
GO

[/code]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources