Last post Aug 06, 2018 02:06 AM by Nan Yu
Aug 02, 2018 12:11 PM|NAF|LINK
i have a folder containing multiple folders inside , and every folder has sub folders , till we reach the last level that is html files , i want to read this structure and insert it into sql related tables so that when i click on a parent node (a folder
) all the sub folders will be displayed as child nodes , till i reach the node that is the html file(s)
any suggestion how to do that ?
Aug 02, 2018 12:18 PM|PatriceSc|LINK
The firsrt problem is to copy this folder structure to SQL tables. You know which table structure you want to use in SQL ? A single item table, or a folder and a file table ? You could also have a look at https://docs.microsoft.com/en-us/sql/relational-databases/blob/filetable-schema?view=sql-server-2017 which
is yet another way to handle files (when storing the file content as well inside your db).
For the latest point see https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017 You
can define a recursive CTE that starts from a row and process all results recursively until you reached a final step.
Aug 02, 2018 12:23 PM|NAF|LINK
Dear Patric thank you for your reply ,
i want a folder and a file table , i want to be able to generate a webpage , explorer like layout . I dont want to create a file table structure in SQL and a shared path so that i can open the filetable in explorer view and drop the files and folders within
then run a query to see them .
Aug 03, 2018 03:17 AM|Nan Yu|LINK
If you just need the folders name and files names and insert to sql table ,you can try something like :
So that you can query the table and create a tree table in your web page .
Aug 03, 2018 04:12 AM|NAF|LINK
hello i have tried the script , its only giving me the directory name and its depth , its not showing the files.
Aug 06, 2018 02:06 AM|Nan Yu|LINK
Try this :
EXEC master.sys.xp_dirtree 'D:\folder',0,1;
If it is a file , file equals 0 :