Load Folder name and Images into SQL Tables

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  December 12, 2014 at 12:36 pm

  #1764561

  robertdba (12/12/2014)

  ---

  Sorry that is the write one.

  SELECT @Cmd = REPLACE(REPLACE(

  'DIR "C:\Car\CarSubfolder\*.jpg" /s /b'

  ,'C:\Car\CarSubfolder\',@pFullPath)

  ,'jpg',@pExtension)

  ;

  INSERT INTO dbo.LoadedImage

  (Folder, SubFolder, Name, Photo)

  SELECT Car,CarSubfolder,<<Name>>,blob.BulkColumn

  FROM OPENROWSET(BULK "C:\Car\CarSubfolder\", SINGLE_BLOB) blob

  ;'

  ,'"' ,'''')

  ,'C:\Car\CarSubfolder\',FUllPathName)

  ,'Car',QUOTENAME(Folder,''''))

  ,'CarSubfolder',QUOTENAME(SubFolder,''''))

  ,'<<Name>>',QUOTENAME([FileName],''''))

  You shouldn't have to make such changes. I provided a stored procedure that simply takes parameters and makes all of the correct substitutions.

  --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)
  

• robertdba

  Mr or Mrs. 500

  Points: 589

  More actions

  December 12, 2014 at 2:19 pm

  #1764575

  So there might something wrong with the query, when I ran it without any changes just the way it is nothing is loaded, maybe I am missing something here.

  Anyways thanks for the help and apologies for taking your time.

  All the best

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  December 12, 2014 at 5:46 pm

  #1764587

  robertdba (12/12/2014)

  ---

  So there might something wrong with the query, when I ran it without any changes just the way it is nothing is loaded, maybe I am missing something here.

  Anyways thanks for the help and apologies for taking your time.

  All the best

  It may be because you might not be used to someone providing a complete solution. The script I provided creates a stored procedure. For how to use the stored procedure, see the section in the header of the proc under "Example Usage":.

  The script also requires that you first execute the code to create the two functions that I also provided code for.

  Before continuing, please take the time to read the header of the stored procedure.

  --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)