Compare documents in folder to items in a table

Question

Compare documents in folder to items in a table

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 1

TheSQLGuru (12/22/2009)
I wish xp_dirtree would give enough information to be able to traverse a directory hierarchy. Anyone know how to do that?
d:\sqlexp\112233
d:\sqlexp\112233\20091211
d:\sqlexp\112233\20091212
d:\sqlexp\112233\20091213
d:\sqlexp\112233\20091214
d:\sqlexp\112233\20091215
d:\sqlexp\112233\20091216
d:\sqlexp\112233\20091217
d:\sqlexp\112244
d:\sqlexp\112244\20091211
d:\sqlexp\112244\20091212
d:\sqlexp\112244\20091213
d:\sqlexp\112244\20091214
d:\sqlexp\112244\20091215
d:\sqlexp\112244\20091216
d:\sqlexp\112244\20091217
I seem to get the following output from xp_dirtree, and this doesn't enable me to do stuff on subfolders (such as rmdir):
112233 1
20091211 2
20091212 2
20091213 2
20091214 2
20091215 2
20091216 2
20091217 2
112244 1
20091211 2
20091212 2
20091213 2
20091214 2
20091215 2
20091216 2
20091217 2
Of course given that I need to do the rmdir, I need xp_cmdshell anyway, or CLR with unsafe ... 🙂

EXEC xp_DirTree 'd:\yourfolder', 0, 1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Again I inquire how I am supposed to traverse the hierarchy of the folder system with that output? Having a depth without any way to identify parent isn't useful, right?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 3

TheSQLGuru (12/24/2009)
Again I inquire how I am supposed to traverse the hierarchy of the folder system with that output? Having a depth without any way to identify parent isn't useful, right?

It IS an ordered output. Any "data copy down" method would work including the "Quirky Update" or, if you don't trust that, a While Loop would do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Jeff Moden (12/24/2009)
TheSQLGuru (12/24/2009)
Again I inquire how I am supposed to traverse the hierarchy of the folder system with that output? Having a depth without any way to identify parent isn't useful, right?
It IS an ordered output. Any "data copy down" method would work including the "Quirky Update" or, if you don't trust that, a While Loop would do.

Yep, it IS an ordered output from the XP. How do I actually process that data though? Seems that it would have to be put in a table of some flavor first, right? And then there is no order to that output - nor any form of parent-child relationship identifier.

Hmm - just thought that perhaps it could be dumped into a table with an identity and then rely on that for ordering.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 5

TheSQLGuru (12/25/2009)

Jeff Moden (12/24/2009)

TheSQLGuru (12/24/2009)
Hmm - just thought that perhaps it could be dumped into a table with an identity and then rely on that for ordering.

Exactly. And, if you use that column as the clustered index, you can use the "Quirky Update (with parallelism disabled uing MAXDOP 1)" to "smear" the data down so that each row has a full path fully aware of it's heritage.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)