Insert file in DB

Question

Insert file in DB

g.maclanca

Valued Member

Points: 57
More actions
December 2, 2011 at 9:18 am

#263930

Hi,
I need to store a exported flat file in the DB, what the best way to do it!
Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

An insert statement? Seriously though, you need to provide a bit more detail. Are you trying to figure out how to store them? Since you are on 2005 the best datatype is varbinary(max). If you have 2008 you might consider FILESTREAM. You might even consider just writing the file to disc with a path in the db.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GSquared SSC Guru Points: 260824 More actions · Answer 2

Are you talking about storing a file, or about importing data from a file into tables?

You can store files in BLOB columns, either directly or as filestream objects.

You can import data from a zillion different file types.

How to best implement either one depends on a lot of factors. But it all starts with what you mean by store a file in a database.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

g.maclanca Valued Member Points: 57 More actions · Answer 3

Thanks for your reply.

I have a package in SSIS that creates (exports) a flat file but afterwards I need to store the file in a table column (image)

My question (as a nobie in ssis) is who can I do it!

Shell I use a SQL Task or is there other way?

Once again thanks in advance

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

I would probably use something like this SQLCLR:

http://nclsqlclrfile.codeplex.com/[/url]

Clarification: I am the author of this SQLCLR

But SSIS does have a data-flow task that can do it, but I have only had a little success with it.

CEWII