February 6, 2007 at 9:17 am
In TSQL, is it possible to grab a file from a folder and store it in a table? And vise vera, export a file that is stored in a record to a given folder? If so, how? I can't seem to find code on this anywhere.
Maybe it would be using sp_OAMethod or something like that.
Thank you,
Mick
February 6, 2007 at 12:20 pm
You can use the new BULK option in OPENROWSET in sql 2005 to easily load files to database. E.g. loading the content of file C:\Test\Orders.txt to a variable:
DECLARE @Text nvarchar(max)
SELECT
@Text=CAST(BulkColumn as xml) FROM OPENROWSET(BULK N'C:\Test\Orders.txt', SINGLE_BLOB) A
February 7, 2007 at 1:33 pm
Thank you for the help. That did not work for me, but helped me get there. This worked...
Now I need to know how to go back the other way and get it out to a new location, but with the file completely intact. The OPENROWSET does not seem to have an export equivalent, and I can't get bcp to export it intact, it messes the file all up.
INSERT
INTO TestTable..Test(FileName, FileType, Document)
SELECT 'Alert.xls' AS FileName, '.xls' AS FileType,
* FROM OPENROWSET(BULK N'd:\SqlTemp\Reports\Alert.xls',SINGLE_BLOB) AS Document
February 13, 2007 at 8:59 pm
Use this link:
http://sql.codebetter.com/blogs/raymond.lewallen/archive/2005/05/04/62781.aspx
Or for text file:
Use
EXEC master..xp_cmdshell 'osql.exe -S Server -U sa -P password -d database -Q "Select * From test" -o "C:\output.txt"'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply