Importing binary files into sql server database

I work for a company that makes heat transfers for the imprinted apparel market. We're developing a database of merchandise images for all of our non-design inventory. Using Access we're going to be inserting thumbnails of psd (photoshop) files. We're wondering if there is any way to import multiple psd's into the sql server database into matching records like matching a column named "filename" and the actual filename of the file without having to upload each file individually. We want to be able to dump the files from the database of the matching records, also. This way, once our catalog designer has found which designs they need to put into the new catalog, it will dump the psd's for us. The same for our staffer who does color separations.

Any suggestions out there? If you need me to post further of what we're trying here, I will. This is for the bossman.store the location and file names and not the files themselves. you may be able to use text\image\blobs but I would reccomend against it. it will bloat your database, probably hamper performance and likely cause unneeded difficulty.|||store the location and file names and not the files themselves. you may be able to use text\image\blobs but I would reccomend against it. it will bloat your database, probably hamper performance and likely cause unneeded difficulty.

As mentioned here (and in other posts, do a search), the general consensus among the community is to store a pointer (link) in the database and store the binary (image file) on a web server or somewhere else.

However, I have run into situations where this was not permitted (courtesy of some rabid security folks) or impractical. I currently manage a pair of 200 GB image repositories and SQL does just fine storing the blobs. Do a google search for "pure asp upload" or similar variants and you will find some sample code for uploading binary streams to a database.

Regards,

hmscott|||at my last gig we measured our image data in the terabytes and there is no way I am bloating my database and my database server with that stuff and we got new stuff all of the time. One week managment came in with 2 terabytes from the Smithsonian. If I store that stuff in SQL Server not only would I have I needed a new machine, which I did, but I would have had to migrate all of my DBs onto a new machine as well.

Besides logisitics, technically blob data is just a pain to handle. I was going back over some Ken Henderson last night and as he points out if you store data in blobs, the data is not really in the row. What you get is a sixteen bit pointer to other data pages and if you do not insert the blob at record creation you do not get a pointer. So it is reccomended to insert a default value. My two cents regarding this is now you are wasting space and inserting junk data. Ken also points out that BLOB data creates a transaction log conundrum. Do you log it and bloat the log or not and compromise your db's integrity.|||Thanks for these suggestions. This is a pretty new trail for us to blaze. Glad to know there are forums like this where we can get questions answered.

Importing binary data tables?

My apologies in advance for hitting the forum with such a basic question...

I've got a large number of raw binary data tables generated by some external software that I'd like to import into a SQL database. I've set up the database tables to hold the information and assigned the table columns the appropriate data types, but I can't figure out how to import the data.

From what I can see there isn't a data import 'wizard' that supports raw binary, and my limited experience with bcp has given me the impression that it only supports ASCII tables. I find it hard to believe that there isn't a DTS procedure to handle this... but I'm definitely stuck...

Thanks,
-fpsivYou can import data by using bcp or bulk insert to text or image fields and then parse data if it needs.|||Thanks for the quick reply snail...

I was working with BULK INSERT via the Query Analyzer... but ran into a brick wall in trying to set up the format file. I can't seem to get the 'column collation' parameter set correctly. I thought that 'collation' applied to character data... is there a correct collation for raw binary?

Thanks,
-fpsiv|||Check this : import image, I was trying to the same for binary file - it works too.

http://www.dbforums.com/t926676.html|||Thanks snail... got it working! I really appreciate your help!

Cheers
-fpsiv|||hmmml ! I tried the same query but it didn't worked :

CREATE TABLE Pictures
(
Pic_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
Images nvarchar(5000)
)

BULK INSERT Northwind.dbo.[pictures] FROM 'c:\bridge.jpg'
WITH (
FORMATFILE = 'c:\bcp.fmt'
)

------------
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

Did I missing something?

Thanks