Store record in database

Question

1.00/5 (2 votes)

See more:

Hey guys,
Main Directory
sub directory 1
Files(Images, Excel, Word, Text, PDF)
sub directory 2
Files(Images, Excel, Word, Text, PDF)
sub directory 2
Files(Images, Excel, Word, Text, PDF)
sub directory 2
Files(Images, Excel, Word, Text, PDF)

Here, I want to store each Files in each sub directory into SQL Server Management Studio.
Can anyone tell me how to do it.

Thanks.

What I have tried:

SQL

USE DB
GO
CREATE TABLE Test1(
Name nvarchar(255) NOT Null, 
Type nvarchar(255) Null, 
BinaryData varbinary(Max) Null, 
Primary Key(Name)
) ON [PRIMARY] 
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO 
--PDF file
Insert Test1([Name],[Type],[BinaryData]) 
Select 'detail1.pdf','PDF file', BulkColumn from Openrowset( Bulk 
'C:\Database\detail1.pdf', Single_Blob) as DATA
Insert Test1([Name],[Type],[BinaryData]) 
Select 'detail2.pdf','PDF file', BulkColumn from Openrowset( Bulk 
'C:\Database\detail2.pdf', Single_Blob) as DATA
Insert Test1([Name],[Type],[BinaryData]) 
Select 'detail1.pdf','PDF file', BulkColumn from Openrowset( Bulk 
'C:\Database\detail3.pdf', Single_Blob) as DATA
Insert Test1([Name],[Type],[BinaryData]) 
Select 'detail1.pdf','PDF file', BulkColumn from Openrowset( Bulk 
'C:\Database\detail4.pdf', Single_Blob) as DATA

--Image file
Insert Test1([Name],[Type],[BinaryData]) 
Select 'image1.png','imagefile', BulkColumn from Openrowset( Bulk 
'C:\Database\image1.png', Single_Blob) as DATA
Insert Test1([Name],[Type],[BinaryData]) 
Select 'image2.png','imagefile', BulkColumn from Openrowset( Bulk 
'C:\Database\image2.png', Single_Blob) as DATA

--Doc file
Insert Test1([Name],[Type],[BinaryData]) 
Select 'doc1.doc','Docfile', BulkColumn from Openrowset( Bulk 
'C:\Database\doc1.doc', Single_Blob) as DATA
Insert Test1([Name],[Type],[BinaryData]) 
Select 'doc2.doc','Docfile', BulkColumn from Openrowset( Bulk 
'C:\Database\doc2.doc', Single_Blob) as DATA

--Text File
Insert Test1([Name],[Type],[BinaryData]) 
Select 'contain.txt','TextFile', BulkColumn from Openrowset( Bulk 
'C:\Database\contain.txt', Single_Blob) as DATA

Posted 3-Jul-18 16:10pm

Member 13889976

Updated 4-Jul-18 18:23pm

Patrice T

v6

Add a Solution

Comments

Mohibur Rashid 3-Jul-18 22:41pm

what's your data volume?
What's your planned database?
Why do you want to store in database?
What do you want to store in database? the file itself or extracted content of the file?

When you say macro, you also need to specify what application are you talking about
Visual studio is editing tool. It's a support tool for developer.
So, 'Macro or visual studio' can not be answered

Member 13889976 3-Jul-18 22:57pm

I want to store PDF, Image, Word,Excel and Text Documents into database.

OriginalGriff 4-Jul-18 1:29am

Yes, we know that - you told us originally.
What you haven't told us is any actual information on what you are trying to do, or what problem you are having doing it!

Read the questions above, and remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

Patrice T 4-Jul-18 2:59am

Try to read again the first comment.

Member 13889976 4-Jul-18 20:47pm

I have changed my scenario. please verify it and give me some idea how to resolve it.

RedDk 4-Jul-18 23:13pm

What is the exact error message you get when you run this TSQL code you've shown.

For future reference include some sort of [EDIT] ... [END OF EDIT] brackets when you alter the original post. In order for "us" to understand where "we're" coming from when "we" comment.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mohibur Rashid · Answer 1 · 2018-07-04T18:23:00

I think I have understood your problem. I haven't found some answer from you though. Anyway,

If you want to automate this process, you need to do some work.
1. Write a script(bat, or how about python?) that can read file list under a given directory and can generate a script which is equivalent to your currently hard coded script.
2. Execute the newly generated script to dump data into your database.

few issues I want to identify:
-> I asked you about your data size and your chosen database. The reason is it is important to know whether your database can handle the volume or not.

If your db is going to be Microsoft Access that is going to be a shot in the foot.