how to load entire contents of file into 1 record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

how to load entire contents of file into 1 record

Author

Topic

sw26p2
Starting Member

8 Posts

Posted - 2009-09-23 : 08:15:29

I just posted this question in the t-sql forum, but maybe I should have posted it here instead

I have a table containing the columns:
- FileName varchar(200)
- FileContent text

I need to write a stored procedure that will open the file in FileName and load the entire contents of the file into the FileContent column.

Is there a way to access the File on the drive, and have it do a "read all" to get entire file?

Thanks for your help.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-23 : 10:56:13

Look up "openrowset" in Books Online...there's actually an example in the article that does exactly what you need:

quote:
D. Using OPENROWSET to bulk insert file data into a varbinary(max) column
The following example creates a small table for demonstration purposes, and inserts file data from a file named Text1.txt located in the C: root directory into a varbinary(max) column.

Copy Code
USE AdventureWorks
GO
CREATE TABLE myTable(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max))
GO

INSERT INTO myTable(FileName, FileType, Document)
SELECT 'Text1.txt' AS FileName,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
GO

- Lumbago

sw26p2
Starting Member

8 Posts

Posted - 2009-09-23 : 15:48:44

Thank You very much!

sw26p2
Starting Member

8 Posts

Posted - 2009-09-25 : 12:02:42

Hi again - I have the OPENROWSET working - but the issue is the having the data stored as binary data. My script will be loading text files containing html code. The data needs to be stored in a "text" data type. I looked to see if there is a way to convert varbinary to text, but the documentation shows cast/convert does not allow this. http://msdn.microsoft.com/en-us/library/ms187928.aspx
Thanks for your help!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-27 : 15:48:08

Did you look in the documentation about OPENROWSET though? It also has the option to use a format file...I believe you should be able to create a generic format file that will fit everything in to one columns. I have never tried myself though...

- Lumbago
http://xkcd.com/327/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources