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

avatar image

tombiernacki
338 20 22 27

(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

avatar image

JohnM
12.4k 3 7 14

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.

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:

x137
x26

asked: May 02, 2012 at 08:06 PM

Seen: 10595 times

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

Copyright 2016 Redgate Software. Privacy Policy