Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
francoisb27
Starting Member
1 Post |
 Posted - 2010-12-13 : 15:43:49
|
Hi all,I am new to SQL and trying to get the following to work but it is causing me headaches ;-) so I wonder if you could help. Basically I would like to compare a table from a database against a folder and get displayed the differences. Now the table looks like products version field another Product A 1.2 .. .. Product B 4.1 .. .. Product F 7.2 .. ..Product D 2.27 .. ..The folder looks like C:\ Products\Product A\ 1.2\folder xx\folder yy\ folder zzC:\ Products\Product B\ 3.4.5\ folder xxC:\ Products\Product B\ 3.4.6\ folder xxC:\ Products\Product B\ 4.1\ folder xx\ folder yyC:\ Products\Product C\ 2.15\folder xxC:\ Products\Product C\ 2.33\folder xx\folder yy\folder zzC:\ Products\Product D\ 2.15\folder xxThe first subfolder always shows the version number so basically a product either matches (e.g. product A and b) or the product version it doesn’t match (product D )or the product doesn't exists (e.g. product c being not in the table and product F not being in the folder).What I would like to get automated is a script that identifies the differences between the table and the folder and shows it to me with the differences so D, C and F and the relevant version nr of either table or folder or both. I figured out that I can get a list from the db by select * FROM dbo. Inst and the folder list from EXEC master..xp_dirtree ‘C:\Products’ but then I have to go through them and compare them manually. Can someone help me to understand how I can get SQL (2005) to do the comparison itself? Could it be the minus or exists command? Would I have to add the results of the dirtree format into a temp table in order then to compare these tables? To add, the product folder can have many sub folders, the only thing fixed is that the version is always in the first subfolder (depth=2) but there could be more than one version and only the latest one is of interest.Any help would be appreciated.Many thanks in advance. |
|
|
|
|
|
how to compare folders agains db table and show di