TSQL Text Import

[lameid]

I am using SQL 2012 and the database permissions are not as open as I would like. I do NOT have the bulk admin role nor tools beyond SSMS.

I need to import around 400 pipe delimited text files with no headers and three different file layouts.

My frontend is Access 2013 but doing this through appending to an ADO recordset seemed painfully slow which leads me to wanting to go native SQL Server for the processing.

On the SQL side I would like to make 3 stored procedures (one for each layout) that takes a file name and path as a parameter. I would then call the appropriate procedure from an application for each file to load the data.

Importing data via the server seems to be the only decent performance.

With my limited tools, permissions and experience I am at a loss as to what is the best way forward.

Is there a good way to directly import the text? Should I consider staging in Access and then importing directly from an Access database with openrowset? Is there a better idea that I don't see yet? In my research I am somewhat confused by the term "Bulk". I think it is just a SQL Server term for native text import?

 

[gmmastros]

Please take a look at this:

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/title-12/

It shows 6 different methods for importing data into SQL Server. I'm certain one of these methods should work for you.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lameid]

Thanks, but no help there.

Import Wizard - 400 files - I am not doing those individually additionally this data is too big to fit in Access, hence SQL
bcp - I can't imagine I have it... given below and I don't think it would work, bulk is in the name and that goes to my question about bulk nomenclature
BULK INSERT - I straight up am not a member of the Bulk Admin Role that gives this permission
No SSIS here

Sometimes I wonder how much budget they have to support me and my department... I'd gladly reallocate it but still participate in licensing pools and communication services.

 

[lameid]

Long story short the program methodology used on the client had an efficiency issue and I am able to load data at a reasonable pace outside of native direct SQL processing.