x

insert into table variable from select statement

Hi, I ran into an issue and am stuck. I am trying to insert a select statement into a table variable.

--select statement is the following

SELECT  
    ps.database_id,
    ps.OBJECT_ID, 
    so.name,
    ps.index_id,
    b.name,
    ps.avg_fragmentation_in_percent,
    b.object_id,
    b.index_id,
    b.type,
    b.type_desc,
    b.fill_factor
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
INNER JOIN sys.objects so on b.object_id = so.object_id
           AND ps.index_id = b.index_id 
WHERE ps.database_id = DB_ID() 
ORDER BY ps.OBJECT_ID

--I declared the table variable below

declare @Index_Table Table(
    Database_id smallint null,
    Object_id int null,
    Index_id int null,
    Name sysname not null,
    Type tinyint null,
    Type_desc nvarchar(60) null,
    Avg_fragmentation_in_percent float null,
    Fill_factor tinyint null,
    RowId int identity(1,1)) 

How Can I make this work??

more ▼

asked May 02, 2012 at 08:06 PM in Default

tombiernacki gravatar image

tombiernacki
338 19 20 23

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You just need to reorder your statements. Declare the temporary table first and then do an INSERT using the SELECT as the operator that will provide the values for the INSERT.

For example:

DECLARE @Index_Table TABLE( Database_id smallint null
 , Object_id int null
 , Index_id int null
 , Name sysname not null
 , Type tinyint null
 , Type_desc nvarchar(60) null
 , Avg_fragmentation_in_percent float null
 , Fill_factor tinyint null
 , RowId int identity(1,1))

 INSERT @INdex_Table (Database_id
    , object_ID 
    , Name
    , Index_ID
    , Type
    , Type_desc
    , Avg_fragmentation_in_percent
    , Fill_factor)
SELECT ps.database_id
, ps.object_ID
, so.name
, ps.index_id
, b.type
, b.type_desc
, ps.avg_fragmentation_in_percent
,b.fill_factor
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
    INNER JOIN sys.objects so ON b.object_id = so.object_id AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() 
ORDER BY ps.OBJECT_ID 

SELECT * FROM @Index_Table
GO
Hope this helps!!
more ▼

answered May 02, 2012 at 09:00 PM

JohnM gravatar image

JohnM
6.9k 1 3 7

Thanks that fixed it
May 02, 2012 at 09:29 PM tombiernacki
Thanks that fixed it
May 02, 2012 at 09:30 PM tombiernacki
Awesome!! Glad it worked for you!
May 03, 2012 at 01:45 AM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x107
x22

asked: May 02, 2012 at 08:06 PM

Seen: 6547 times

Last Updated: May 03, 2012 at 11:56 AM