Image datatype(BLOB) to a text file--????

Question

Post reply

Image datatype(BLOB) to a text file--????

Pac123

SSCrazy

Points: 2464
More actions
July 31, 2009 at 3:43 pm

#132578

Hi,
I am looking at a way as to export records that have image datatype from SQL Server 2005 to A Flatfile/Textfile. This cannot be done with standard import/export tools or SSIS.
How can this be done most efficiently , since i have thousands if image records that need to be exported.
In my research i have come across the textcopy utility , i am not sure if this is in SQL 2005 or if i would have to export one file at a time??
Please provide your suggestions...i would welcome any different ways of doing this.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Allister Reid SSCrazy Points: 2666 More actions · Answer 1

Hi,

What are you trying to achieve? Has the data to be transferred to another system?

Pac123 SSCrazy Points: 2464 More actions · Answer 2

Yes...another DB system...but prior to putting it in the destination area it needs to be stored in a flatfile format, for the staging area

Allister Reid SSCrazy Points: 2666 More actions · Answer 3

And you can't do a INSERT INTO StagingTable SELECT FROM SourceTable?

Pac123 SSCrazy Points: 2464 More actions · Answer 4

Pac123

SSCrazy

Points: 2464

July 31, 2009 at 4:21 pm

#1033199

No..

Allister Reid SSCrazy Points: 2666 More actions · Answer 5

How about (something like)..

On source server:

CREATE DATABASE TempTransfer

CREATE TABLE TempTransfer.dbo.DataToTransfer (ID, ImageBlob)

INSERT INTO TempTransfer.DataToTransfer

SELECT ID, ImageBlob FROM SOURCE

BACKUP DATABASE TempTransfer TO DISK 'C:\TempTransfer.bak'

On destination server:

RESTORE DATABASE TempTransfer FROM DISK 'C:\TempTransfer.bak'

UPDATE DestinationDatabase.dbo.DestinationTable

SET ImageBlob = TempTransfer.dbo.DataToTransfer.ImageBlob

FROM TempTransfer.dbo.DataToTransfer T

WHERE DestinationTable.ID = T.ID

Pac123 SSCrazy Points: 2464 More actions · Answer 6

Thaks Allister, but i cannot transfer directly between 2 DB systems. I have to transfer the source data to a text/flatfile first...

Allister Reid SSCrazy Points: 2666 More actions · Answer 7

I was suggesting making a temporary database with just table with images in it, backing up this database to a .bak file, transferring the bak file (over network/dvd/however) to the destination server and restoring temporary database then moving data into the staging table.