Book for architect

97 Things Every Software Architect Should Know

Loading large files as attachment in sql server

CREATE TABLE [dbo].[TEMP_ATTACH](
[DOCID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FILE_NAME] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ATTACHMENT] [varbinary](max) NULL,
[FULL_NAME] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

declare @path varchar(100)
declare @filename Nvarchar(500)
SET @path = 'C:\Attachments\'
declare att_cursor cursor fast_forward for
select [full_name] from temp_attach

open att_cursor
fetch next from att_cursor into
@filename

while @@fetch_status = 0
begin

EXEC ('UPDATE TEMP_ATTACH
SET ATTACHMENT = BulkColumn
FROM OPENROWSET (BULK ''' + @path + @filename + ''', SINGLE_BLOB) MyFile
WHERE [full_name] = ''' + @filename + '''')

fetch next from att_cursor into
@filename
end
CLOSE att_cursor
DEALLOCATE att_cursor