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??
Best Answer
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!!
