Create folder Structure in Batch using SQL script output -

i have sql table holds records organised hierarchically follows:

id item  othercolumns parentid 1        blah          null 2   b      blah           1 3   c      blah           2 4   d      blah           3 5   e      blah           3 6   f      blah           3 7   g      blah           4 8   h      blah           4 9        blah           4 10  j      blah           4 11  k      blah           6 12  l      blah           6 13  m      blah           6 14  n      blah           6 

graphically, hierarchy is

a --b ----c -------d ----------g ----------h ----------i ----------j -------e -------f ----------k ----------l ----------m ----------n 

i have sql script takes parentid parameter , returns children node.

i call script recursive function in batch file , print out the items in order of structure i'm not sure how can create folder structure mirrors this

my batch is

@echo off setlocal enabledelayedexpansion   set id=1 call :createsubdirectories !id! 1  goto eof  :createsubdirectories      /f "tokens=1,2,*" %%i in ('sqlcmd -s myserver\myinstance -d mydatabase -u myuser -p mypassword -i c:\path\to\my\script.sql -v parentid^=%~1! -h -1') (         echo %%i         call :createsubdirectories %%i     )  :eof  endlocal  

so prints out

a b c d g h j e f k l m n 

however, if add mkdir loop, folders create on same level. how can create folder structure represented above?

run mkdir against cte output

declare @foo table (id int, item  varchar(100), othercolumns varchar(20), parentid int)  insert @foo values (1   ,'a', 'blah', null), (2   ,'b', 'blah',  1  ), (3   ,'c', 'blah',  2  ), (4   ,'d', 'blah',  3  ), (5   ,'e', 'blah',  3  ), (6   ,'f', 'blah',  3  ), (7   ,'g', 'blah',  4  ), (8   ,'h', 'blah',  4  ), (9   ,'i', 'blah',  4  ), (10  ,'j', 'blah',  4  ), (11  ,'k', 'blah',  6  ), (12  ,'l', 'blah',  6  ), (13  ,'m', 'blah',  6  ), (14  ,'n', 'blah',  6  );  hierarchy (     select id, folderpath = item, othercolumns, parentid, 1 level @foo parentid null     union     select [@foo].id,            left(concat(hierarchy.folderpath, '\', [@foo].item), 100),            [@foo].othercolumns,            [@foo].parentid,            level + 1              hierarchy join @foo on [@foo].parentid = hierarchy.id  ) select folderpath hierarchy order hierarchy.level; 

gives

a a\b a\b\c a\b\c\d a\b\c\e a\b\c\f a\b\c\f\k a\b\c\f\l a\b\c\f\m a\b\c\f\n a\b\c\d\g a\b\c\d\h a\b\c\d\i a\b\c\d\j