Compare a table with physical path

• Minnu

  SSCertifiable

  Points: 6451

  More actions

  March 31, 2015 at 6:59 am

  #295625

  Hi Team,

  i have a table with two columns

  id | filepath

  --------------------------------------------------

  1| D:\Doc files\The Best\HHT.JPG

  2| D:\Doc files\The Best\sealed_pack.txt

  3| D:\Doc files\The Best\lsbom.JPG

  4| D:\Doc files\The Best\moc.png

  5| D:\Doc files\The Best\stock.txt

  6| D:\Doc files\The Best\depot.JPG

  and in a physical system there are more files than the table.

  D:\Doc files\The Best\HHT.JPG

  D:\Doc files\The Best\sealed_pack.txt

  D:\Doc files\The Best\JKS\lsbom.JPG

  D:\Doc files\The Best\moc.png

  D:\Doc files\The Best\stock.txt

  D:\Doc files\The Best\GDN\depot.JPG

  D:\Doc files\The Best\CASA.JPG

  D:\Doc files\The Best\SO.txt

  D:\Doc files\The Best\BA.JPG

  i want to compare the filepath column in table with physical drive files and

  get the details of files which in table and not in physical and viceversa

  Please help

• Matt Simmons

  SSCrazy

  Points: 2846

  More actions

  March 31, 2015 at 7:30 am

  #1787704

  Assuming that you have hundreds or thousands of files and a manual comparison would be too time consuming I would create an SSIS Package using a For Each Container and load the list of physical files into a Temp Table then use a Left Join between your two tables to determine the delta.

  If your table is set up correctly, with constraints on the file name, you could probably get away with doing an insert directly into your table. Those that already exist will fail, those that don't will succeed, agian doing this in a For Each Container in SSIS.

  Regards,
  Matt

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 31, 2015 at 7:38 am

  #1787713

  If I had thousands of files, I think I'd prefer to use xp_cmdshell to run dir /b and pull all the file names into the staging table in one go. That should be quicker than looping through all the files one by one.

  John

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  March 31, 2015 at 7:52 am

  #1787723

  Also you can use xp_dirtree SP to get a table of files and dirs in the target physical location.

  Note the path should be specified for your MSSS machine.

  EXEC master.sys.xp_dirtree 'D:\Doc files\The Best',1,1;

  Then compare tables using EXCEPT.