Help with "table data type"

Question

Help with "table data type"

billy_pang

SSC Journeyman

Points: 83
More actions
July 25, 2002 at 5:45 pm

#158749

Hello:
does anyone have an example of how to insert values into a table data type from rows produced by an EXEC statement?
I can do it with a temp table but would like to do with a table data type, as shown below:
create table #temp_dbInfo (
dbInfo_name varchar(50),
dbInfo_db_size varchar(25),
dbInfo_owner varchar(50),
dbInfo_dbid int,
dbInfo_created datetime,
dbInfo_status varchar(255),
dbInfo_compatibility_level smallint)
GO
insert into #temp_dbInfo exec sp_helpdb
GO
select * from #temp_dbInfo
GO
drop table #temp_dbInfo
GO
but... it doesn't work with the table data type, as shown below:
DECLARE @dbInfo table (
dbInfo_name varchar(50),
dbInfo_db_size varchar(25),
dbInfo_owner varchar(50),
dbInfo_dbid int,
dbInfo_created datetime,
dbInfo_status varchar(255),
dbInfo_compatibility_level smallint)
insert @dbInfo exec sp_helpdb
GO
any ideas?
Thanks in advance,
Billy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 1

Hi, from BOL: -

Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

Regards,

Andy Jones

.

kmaclure SSC Journeyman Points: 80 More actions · Answer 2

Hey there,

I don't know if this will help but have you heard of / tried this:

EXEC sp_serveroption [<servername>] , 'data access', 'true'

SELECT * INTO #<tablename> FROM OPENQUERY([<servername>], 'exec sp_who')

SELECT * FROM #<tablename>

This is one way to save the data from an SP without creating the Table first.

Use it / don't use it, your perogative

Keith

"It makes me want to run around the Server Room in a Super-Hero Costume"

billy_pang SSC Journeyman Points: 83 More actions · Answer 3

thanks

however, BOL only says you can't use INSERT INTO but it didn't say that you can't use INSERT. I wonder if there is another way.

Billy

Edited by - billy_pang on 07/26/2002 09:46:15 AM

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Sorry you cannot do this. You could do with a temp table then add a int IDENTITY column and use a while loop with variables to move the data from the temp table to the table variable using INSERT on the table variable or via cursor. Also as for SELECT INTO there are know locking issues when used with a temp table, it is better to use insert into like you have in your first code.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)