Loading files into table

• David Mulford

  Newbie

  Points: 1

  More actions

  July 13, 2006 at 10:06 am

  #173465

  Hey all!

  I have ~700 files on a local hard drive that I need to have transferred into a table. Can I do this using only DTS, or would I have to write some .NET code to loop through all the files and save them?

  I already have the .NET functionality to save one file being uploaded by a user through a web interface, but this won't help me in taking all the files from the drive to the database.

  Any help getting me started with this is appreciated. Thanks in advance!

• stax68

  SSChampion

  Points: 11711

  More actions

  July 13, 2006 at 12:57 pm

  #649082

  Since this is the TSQL board and since this is presumably an admin rather than production task [edit: I'm thinking of security issues in particular], I'll suggest

  select @strvar = 'dir "' + @filepath ... + '"'

  insert <table> exec xp_cmdshell  @strvar --to get files

   

  ...

   

     <cursor through <table> FOR UPDATE OF logging fields>

   

        <for each row:>

        (

        select @strvar = 'bcp ' + ... @filepath + @filename ...

        exec @retval = xp_cmdshell @strvar

        <update log fields>

        )

   

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant