I am trying to use the procedure xp_dirtree with an insert statement that is inside a while loop.
The sql below starts of by populating a table called dirtemp with the subdirectories for the given path 'C:\Temp'
it also adds a RowID to the table.
The sql then creates another table dirtemp2, which is designed to hold the subdirectory and their files.
The aim is to use the table dirtemp to loop through each subdirectory in the given path and populate dirtemp2 with the files.
At the moment the table dirtemp2 doesn't populate.
Is it possible to use the xp_dirtree in a while statement and if so how. Can you pass other variables to insert statement within the loop.
drop table dirtmp
drop table dirtmp2
create table dirtmp ( RowID int identity(1, 1),[Subdirectory] nvarchar(1000), [Depth] int
insert into dirtmp(Subdirectory,Depth) exec master..xp_dirtree 'C:\Temp',1
create table dirtmp2 ([Files] nvarchar(1000),[Subdirectory] nvarchar(1000)
declare @NumberRecords int, @RowCount int
declare @path nvarchar(1000),@filepath nvarchar(1000), @subdirectory nvarchar(1000)
-- Get the number of records in the temporary table
set @NumberRecords = @@ROWCOUNT
set @RowCount = 1
set @path = 'C:\Temp\'
-- loop through all records in the temporary table
-- using the WHILE loop construct
while @RowCount <= @NumberRecords
select @subdirectory = Subdirectory, @filepath = @path + Subdirectory
where RowID = @RowCount
insert into dirtmp2(Subdirectory,Files)
@subdirectory, exec master..xp_dirtree @filepath
set @RowCount = @RowCount + 1
select * from dirtmp
select * from dirtmp2