Click here to Skip to main content
14,664,409 members
Rate this:
Please Sign up or sign in to vote.
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:

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
Updated 4-Jul-18 18:23pm
v6
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100