Search This Blog

TexCopy Sql Server 2005

Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
SQL 2005, you can accomplish the same result in Transact-SQL using
OPENROWSET...BULK. The proc example below imports file contents into a temp
table and then updates the specified main table row with the blob data.


CREATE PROC dbo.TextCopy
@MyPK int,
@FileName varchar(255)
AS /*

Use this Sp to copy the BLOB files to the an image column , works as the same as textcopy.exe
*/
BEGIN



DECLARE @nSql nvarchar(MAX)


CREATE TABLE ##temp ( blob varbinary(MAX) )


SET @nSql = N'
INSERT INTO ##temp
SELECT img.*
FROM OPENROWSET
(BULK ''' + @FileName + ''',
SINGLE_BLOB) img'

-- PRINT @nSql
EXEC sp_executesql @nSql


UPDATE dbo.MyTable
SET MyBlob = ( SELECT blob
FROM ##temp )
WHERE MyTable.MyPK = @MyPK


DROP TABLE ##temp
END

No comments: