locked
Insert varbinary data using command-line Sqlcmd RRS feed

  • Question

  •  

    Hi,

     

    I have a SQL script file which creates a table and and inserts all data required by the client app into the table.

     

    The table also holds images in a varbinary field. How do I insert an image held on the file system (e.g. "C:\Images\MyImage.gif") into a varbinary field using a script that is run using sqlcmd on the command line?

     

    Any pointers greatly appreciated!

     

    Mike

    Friday, May 23, 2008 4:14 PM

Answers

  • Use opendrowset and insert as a single_blob.

     

     

    Code Snippet

    CREATE TABLE #temp (

    dt DATETIME,

    col1 VARBINARY(MAX)

    )

    INSERT #temp

    SELECT GETDATE(), *

    FROM OPENROWSET(BULK 'C:\picture.jpg',

    SINGLE_BLOB) AS x;

     

     

    Friday, May 23, 2008 4:17 PM
  • Since you specified you wanted to do this from sqlcmd you could use:

     

    sqlcmd -E -i c:\insertscript.sql 

     

    The -i passes a script file that would contain the insert statement.

    Friday, May 23, 2008 4:34 PM

All replies

  • Use opendrowset and insert as a single_blob.

     

     

    Code Snippet

    CREATE TABLE #temp (

    dt DATETIME,

    col1 VARBINARY(MAX)

    )

    INSERT #temp

    SELECT GETDATE(), *

    FROM OPENROWSET(BULK 'C:\picture.jpg',

    SINGLE_BLOB) AS x;

     

     

    Friday, May 23, 2008 4:17 PM
  • Since you specified you wanted to do this from sqlcmd you could use:

     

    sqlcmd -E -i c:\insertscript.sql 

     

    The -i passes a script file that would contain the insert statement.

    Friday, May 23, 2008 4:34 PM
  • Perfect! Thank you!

     

    Friday, May 23, 2008 5:03 PM
  • That works like a champ.  What if I wanted to load all of the images in a directory?

    Steve

     

    Tuesday, July 15, 2008 8:57 PM
  • Welcome Steve.  If I understand your question correctly, you would like a way to specify a directory and then have a procedure or function to iterate through all of the files in that directory to bulkload them?  This is not available out of the box, but I got an idea.  Are you open to a common language runtime procedure that would do this?

     

    Tuesday, July 15, 2008 10:05 PM
  • Steve,

         Great idea!!  I have included the code and comments for such a clr proc below.  Hope this helps.  Let me know if this is what you were thinking.  There are some obvious limitations, such as supplying values for additional columns in a table, but this should provide as a good launching point. 

     

    Hope this helps.

     

    Code Snippet

    Imports System

    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server
    Imports System.IO
     
     
    Partial Public Class clr_sp_bulkload
        <Microsoft.SqlServer.Server.SqlProcedure()> _
        Public Shared Sub clr_sp_bulkload(ByVal clm As SqlString, ByVal tbl As SqlString, _
        ByVal dir As SqlString, ByVal extn As SqlString)
     
            'Declare variables and assign the values form the sql parameters
            'Trim the sql parameters to remove leading or trailing spaces that
            'might cause issues in code
     
            Dim sp As SqlPipe = SqlContext.Pipe()
            Dim cmd As New SqlCommand()
            Dim column As String = clm.ToString.Trim
            Dim table As String = tbl.ToString.Trim
            Dim dr As String = dir.ToString.Trim
            Dim flnm As String
            Dim ext As String = extn.ToString.Trim
     
            'Assure tha the directory exists
            If Directory.Exists(dr) Then
     
                'If the directory exists then loop through the files in the directory
                'path using the optional argument of the search criteria
                For Each flnm In Directory.GetFiles(dr, ext)
                    Dim fi As New FileInfo(flnm)
     
                    'Enter the try block to assure that there is not a lock on
                    'the file that would cause an error
     
                    Try
     
                        'The command is built using the input parameters
                        cmd.CommandText = " INSERT " & table & _
                        " SELECT * FROM OPENROWSET(BULK '" & _
                        fi.FullName.ToString & "',SINGLE_BLOB) AS x;"
     
                        'This will send the insert command to the Messages tab which will make it
                        'easier to troubleshoot as it executes the query
                        sp.Send(" INSERT " & table & _
                        " SELECT * FROM OPENROWSET(BULK '" & _
                        fi.FullName.ToString & "',SINGLE_BLOB) AS x;")
                        sp.ExecuteAndSend(cmd)
     
                    Catch ex As Exception
                        sp.Send("File " & fi.FullName.ToString & " is not accessible")
     
                    End Try
     
                Next
     
            Else
                sp.Send("The direcory does not exists or is inaccessible")
            End If
     
        End Sub

    End Class

    SQL CODE

    Alter the proc to set a default value for the extn input parameter that will accept all files

    ALTER PROCEDURE [dbo].[clr_sp_bulkload]
         @clm [nvarchar](4000),
         @tbl [nvarchar](4000),
         @dir [nvarchar](4000),
         @extn [nvarchar](4000) = '*.*'
    AS
    EXTERNAL NAME [clr_sp_bulkload].[clr_sp_bulkload.clr_sp_bulkload].[clr_sp_bulkload]
    GO

     

     Create a temp table to test the procedure

    CREATE TABLE #temp(
    col1 VARBINARY(MAX)

    )

    Excute the proc

     
    EXEC dbo.clr_sp_bulkload 'col1', '#temp', 'C:\Documents and Settings\user\My Documents'
     
    SELECT *
    FROM [#temp]
     

     

     

     

     

    Please review this and other sql clr examples at:

    http://www.sqlclr.net

    Wednesday, July 16, 2008 11:14 AM