Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

RE: Bulk Insert

Status
Not open for further replies.

allyne

MIS
Joined
Feb 9, 2001
Messages
410
Location
US
Hi Everyone,

I have the following sql statement that Bulk Inserts a .iff file from a folder. It looks like this.

BULK INSERT Received
FROM '\\Admfs1\users\CPurnell\Responses\C0009344.iff'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR =' {CR}{LF}\n'
)

This works great for one file. What I really need is to bulk insert all .iff files from the Responses folder. Something like this.

BULK INSERT Received
FROM '\\Admfs1\users\CPurnell\Responses\%.iff'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR =' {CR}{LF}\n'
)


Is there a way to do this in a sql statement?

Thanks for your help!


 
Depending on what version of SQL you are running you should be able to mix in some SQL and VB/C# and code up a for loop that sifts through all the files in the directory.

-Sometimes the answer to your question is the hack that works
 
Thanks for your quick response! I'm using SQL 2005 but unfortunately I do not know VB/C#. I only know SQL....And was self taught at that.....

Any suggestions?
 
the code for working with the file system is very minimal and is almost readable.

Psuedo Code:
For each oFile as File in FileDirObj.Files
if oFile.Extension = "iff"
'pass file name to bulk inserter
end if
Next

-Sometimes the answer to your question is the hack that works
 
I believe SSIS can do this as well. It is not so easy to learn how to use SSIS but once you make the time investment, you will find it very powerful and useful.

"NOTHING is more important in a database than integrity." ESquared
 
Hi SQL Sister,

I created an SSIS package but I kept getting an Access Denied error message even though I have all the permissions needed. After days of research I was told that this is a known bug so I thought I'd try this route. This seems to work great if I can figure out how to import all files in the folder. Unfortunately this seems to be beyond my knowledge.

Thanks for your help!
 
What if you had a separate step that loaded all the file names into a table and you had a little flag for if they were imported or not?

I don't know if you have a dev there or not, but they should be able to whip up a little one button form for loading that data into the database. it shouldn't take more than 2-4 hours for the form and db insert code...

-Sometimes the answer to your question is the hack that works
 
Hmmmm....Interesting idea. Worth checking out.

Thanks!
 
I'm just starting to learn SSIS but I would suspect the permissions are for the SQLServerAgent not having access to the file folder not your personal account. Maybe someone who has been running SSIS can confirm that?

"NOTHING is more important in a database than integrity." ESquared
 
Hi SQL Sister,

Just checked. SQL Server Agent has permissions. What is strange is that If I tell it to insert a particular file in that folder it works just fine. It seems to error out only when I want to insert a files in that folder.....

Thanks for your help!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top