I had faced couple of cases where I need to store files inside the SQL Server rather than storing these files on the file system. for the cases I faced, I compared the value of storing the files inside SQL server versus storing those files on the file system and start interact with the file system and I found going with storing these files in SQL server is much valuable for me which may be the same case for you as well.
To store the files on sql server do the followings:
|
SELECT TOP 1 * FROM OPENROWSET (BULK N’<FilePath>’, SINGLE_BLOB) TblName |
the above statement will read the file contents and convert it into Binary format (which is the format of BLOB on DB). Make sure to replace <FilePath> with the correct path at your DB Server. meaning this path should be a valid path on DB Server.
after reading the file contents; you can push this content into your DB Tables using ordinary Insert Statement.
|
Insert into Tbl (FileContent) SELECT TOP 1 * FROM OPENROWSET (BULK N’<FilePath>’, SINGLE_BLOB) TblName |
To Read file Contents from DB follow the following steps:
1. Open Command Prompt on SQL Server
2. Type the following Command:
|
C:>bcp “<SQL STATMENT>” queryout <OutputFilePath> -T –d <DBNAME> |
make sure to replace the following placeholders:
- <SQL STATEMENT> by your SQL Statement that retrieving the file contents
- <OutputFilePath> the destination file path including filename
- <DBNAME> your DB Name
3. There will be couple of questions , answer them as below
Enter the file storage type of field Media [image]: <Enter>
Enter prefix-length of field Media [4]: 0
Enter length of field Media [0]: <Enter>
Enter field terminator [none]: <Enter>
Do you want to save this format information in a file? [Y/n] y
I hope you will find this article helpful… Please let me know what do you think by posting your comments