Convert/Insert all files in a directory

• timSF

  SSC Veteran

  Points: 287

  More actions

  February 24, 2015 at 6:03 pm

  #298037

  My task is to convert jpeg's to binary and then insert them into a table called "images". I need to convert/insert all jpeg files in a directory. I'm able to accomplish the task if the files are numbered. The query below works by retrieving one file at a time based on the value of @i. However, I also have directories where the files are not numbered but have ordinary text names like "Red_Sofa.jpg". I need to iterate through these directories as well and convert/insert the jpeg's. I'm running SSMS 2014 Express on 4.0 and Windows 7. I appreciate the help.

  DROP TABLE images

  CREATE TABLE images

  (

  image_name varchar(500) null

  ,image_data varbinary(max) null

  )

  DECLARE @i int

  SET @i = 951

  WHILE (@i <= 951)

  BEGIN

  DECLARE @SQL varchar(MAX)

  SELECT @SQL =

  'INSERT INTO images (image_name, image_data)

  SELECT

  ' + convert(nvarchar(5), @i) + ' AS image_name,

  BulkColumn FROM OpenRowSet ( Bulk ''C:\DB\' +

  convert(nvarchar(5), @i) +

  '.jpg'', Single_Blob) AS image_data'

  EXEC (@SQL)

  SET @i = @i + 1

  END

  GO

• Sporidium

  SSCommitted

  Points: 1550

  More actions

  February 27, 2015 at 4:46 am

  #1780520

  Google the (undocumented :ermm:) extended stored procedure xp_dirtree - Patrick Keisler has a useful blog post.

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  March 20, 2015 at 9:54 pm

  #1785497

  Here is a PowerShell example:

  #env settings

  $srv = "orlando-surface\sql2012"

  $db = "tempdb"

  $dir = "C:\@\"

  #########################################################################

  #nothing below here changes

  #build connection string

  $connStr = "Server=$srv;Database=$db;Trusted_Connection=True;"

  #connect to the database

  $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)

  $conn.Open()

  #get list of files from directory

  foreach($file in (Get-ChildItem $dir | where {-not $_.PSIsContainer}))

  {

  $filename = $file.FullName

  $name = $file.Name

  $binData = [System.IO.File]::ReadAllBytes($filename)

  #prepare

  $cmd = New-Object System.Data.SqlClient.SqlCommand("Insert into images (image_name, image_data) values(@image_name, @image_data)", $conn)

  $cmd.CommandType = [System.Data.CommandType]'Text'

  $cmd.Parameters.Add("@image_name", [System.Data.SqlDbType]'VarChar')

  $cmd.Parameters["@image_name"].Value = $name

  $cmd.Parameters.Add("@image_data", [System.Data.SqlDbType]'VarBinary')

  $cmd.Parameters["@image_data"].Value = $binData

  #execute

  $cmd.ExecuteNonQuery()

  }

  $conn.Close()

  $conn.Dispose()

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato