Monday, May 28, 2007

How to Store pdf,txt doc files in Tables MSSQL SERVER 2005

Hi,

1. Create table with varbinary(max) column(s)
2. Use BCP or BULK INSERT or OPENROWSET(BULK... or create an SSIS Package to transfer the data from flat file to SQL

Example:
create table #ORStable (doclen bigint, doc varbinary(max))
insert into #ORStable
select len(bulkcolumn), *
from
openrowset(bulk 'C:\test.doc', SINGLE_BLOB)
as r

select *
from #ORStable


To use BULK INSERT you need a format file.
Unfortunately, the format file requires the exact size of the word doc

Format file definition: (worddoc.fmt)
Code Snippet 9.0 1 1 SQLBINARY 0 20480 "" 1 doc ""


create table #BItable (doc varbinary(max))
BULK INSERT #BItable
FROM 'C:\test.doc'
WITH ( FORMATFILE= 'C:\worddoc.fmt', CODEPAGE= 'RAW' )

select len(doc), *
from #BITable

To export the data back to a .doc file recreate table #ORStable (doclen bigint, doc varbinary(max))
insert into #ORStable
select len(bulkcolumn), *
from
openrowset(bulk 'C:\test.doc', SINGLE_BLOB)
as r

select *
from #ORStable


To use BULK INSERT you need a format file.
Unfortunately, the format file requires the exact size of the word doc

Format file definition: (worddoc.fmt)
Code Snippet 9.0 1 1 SQLBINARY 0 20480 "" 1 doc ""


create table #BItable (doc varbinary(max))
BULK INSERT #BItable
FROM 'C:\test.doc'
WITH ( FORMATFILE= 'C:\worddoc.fmt', CODEPAGE= 'RAW' )

select len(doc), *
from #BITable

To export the data back to a .doc file requires using BCP and a format file (again with the exact size)

Working with the .txt files and varchar(max) is a little easier, but you need varbinary to save the exact images of the PDF and DOC files.quires using BCP and a format file (again with the exact size)

Working with the .txt files and varchar(max) is a little easier, but you need varbinary to save the exact images of the PDF and DOC files.

No comments: