Solved

bulk insert (image data type)

Posted on 2000-03-13
2
397 Views
Last Modified: 2008-03-04
Trying to figure out the best way to insert an image file into a table. Is this possible through the query analyzer with
bulk insert...
from c:\picture.bmp

or does this have to be done in a binary format...??
Thanks.
0
Comment
Question by:Your_Momma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 8

Accepted Solution

by:
chigrik earned 100 total points
ID: 2613367
1. The following text shows, how you can insert the image from a file (you may use textcopy utility for this purpose).


CREATE TABLE dbo.tbImage (
        ID int IDENTITY (1, 1) NOT NULL ,
        fImage image NULL ,
        CONSTRAINT PK_Image PRIMARY KEY  CLUSTERED
        (
                ID
        )
)
GO

CREATE PROCEDURE sp_imageins(
  @srvname     varchar (30),
  @user_id     varchar (30),
  @password    varchar (30),
  @dbname      varchar (30),
  @tbname      varchar (30),
  @colname     varchar (30),
  @filename    varchar (30),
  @vhereclause varchar (40))
AS
DECLARE @execstring varchar (255)
insert into tbImage (fImage) values ('1')
select @execstring =
         'textcopy /S ' + @srvname +
         ' /U ' + @user_id +
         ' /P ' + @password +
         ' /D ' + @dbname +
         ' /T ' + @tbname +
         ' /C ' + @colname +
         ' /W "' + @vhereclause +
         '" /F ' + @filename +
         ' /I'
exec master..xp_cmdshell @execstring
GO


Parameters for example (from my computer):

sp_imageins 'ALEXANDER', 'sa', 'author', 'test', 'tbImage', 'fImage', 'c:\winnt\winnt.bmp', ' WHERE ID=1 '

2. Another way.
You can use AppendChunk() and GetChunk() method.
Location: SQL Server Books Online
Title:    Using Long Data Types

It's example in ADO (from SQL Server Books Online):

"
This example shows how to use ADO with SQLOLEDB to read and write image data. The critical routines are the while loops that copy the long data (image) to a variable and write the variable to a record in chunks (using the GetChunk and AppendChunk methods).


Before setting up the destination table in this example, make sure to run the sp_dboption stored procedure:

EXEC sp_dboption 'pubs', 'Select into/bulkcopy', 'True'

The destination table is a copy of the pub_info table in the pubs database. Create the table by running:

USE pubs

SELECT * INTO pub_info_x

   FROM pub_info

GO


The pub_info_x table is the destination table in which the long data will be inserted.

The ADO code is:

Public Sub AppendChunkX()

 

    Dim cn As ADODB.Connection

    Dim rstPubInfo As ADODB.Recordset

    Dim strCn As String

    Dim strPubID As String

    Dim strPRInfo As String

    Dim lngOffset As Long

    Dim lngLogoSize As Long

    Dim varLogo As Variant

    Dim varChunk As Variant

 

    Const conChunkSize = 100

 

    ' Open a connection.

    Set cn = New ADODB.Connection

    strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;"

 

   cn.Provider = "sqloledb"

   cn.Open strCn

 

   'Open the pub_info_x table.

   Set rstPubInfo = New ADODB.Recordset

   rstPubInfo.CursorType = adOpenDynamic

   rstPubInfo.LockType = adLockOptimistic

   rstPubInfo.Open "pub_info_x", cn, , , adCmdTable

 

   'Prompt for a logo to copy.

   strMsg = "Available logos are : " & vbCr & vbCr

 

   Do While Not rstPubInfo.EOF

      strMsg = strMsg & rstPubInfo!pub_id & vbCr & _

        Left(rstPubInfo!pr_info,

         InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr

      rstPubInfo.MoveNext

   Loop

 

   strMsg = strMsg & "Enter the ID of a logo to copy:"

   strPubID = InputBox(strMsg)

 

   ' Copy the logo to a variable in chunks.

   rstPubInfo.Filter = "pub_id = '" & strPubID & "'"

   lngLogoSize = rstPubInfo!logo.ActualSize

   Do While lngOffset < lngLogoSize

      varChunk = rstPubInfo!logo.GetChunk(conChunkSize)

      varLogo = varLogo & varChunk

      lngOffset = lngOffset + conChunkSize

   Loop

 

   ' Get data from the user.

   strPubID = Trim(InputBox("Enter a new pub ID:"))

   strPRInfo = Trim(InputBox("Enter descriptive text:"))

 

   ' Add a new record, copying the logo in chunks.

   rstPubInfo.AddNew

   rstPubInfo!pub_id = strPubID

   rstPubInfo!pr_info = strPRInfo

   lngOffset = 0   ' Reset offset.

 

   Do While lngOffset < lngLogoSize

      varChunk = LeftB(RightB(varLogo, lngLogoSize - _

        lngOffset),conChunkSize)

      rstPubInfo!logo.AppendChunk varChunk

      lngOffset = lngOffset + conChunkSize

   Loop

 

   rstPubInfo.Update

 

   ' Show the newly added data.

   MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _

     "Description: " & rstPubInfo!pr_info & vbCr & _

     "Logo size: " & rstPubInfo!logo.ActualSize

 

   rstPubInfo.Close

   cn.Close

 

End Sub
"
0
 

Author Comment

by:Your_Momma
ID: 2613395
Not quite what I was looking for.
Using :
exec master..xp_cmdshell @execstring

is not really an option. But thanks anyway... :-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question