I am not a DBA on this one and I will not have access to BCP or anything like that. So I'm wondering if there is still a way to do it in SQL keeping it very basic.

I have 100's of text files that contain 1000's or lines of data. Each line has a fixed length data format.

For example:

001     abc     test file ***
row 1     Test Data is here!       001
footerId 123 Footer for 001

Then I have a file format of

  • recordId starts at 1 length 8
  • recordName starts at 9 length 8
  • headerDesc starts at 17 length 10
  • rownumber starts starts at 27 length 3
  • rowNumber starts at 1 length 10
  • rowData starts at 11 length 25
  • rowId starts at 36 length 3
  • footerId starts at 1 length 12
  • footerref starts at 13 length 14

I hope that makes sense... Obviously that is not real data. Basically I need to load these files into a table (or many tables). What would be the most basic way to do this in SQL?

I was playing around with the OPENROWSET command but that jammed everything into 1 row. If there was a way to insert a temp table with each line of data I could maybe parse that out into more permanent tables. But it would be great if there was a way to insert the data into an auto generated table.

Is there anyway to do this?

  • 3
    How about using SSIS ? If you are going to import the files frequently, SSIS would be the best solution to put in place.
    – Kin Shah
    May 14, 2013 at 13:35
  • I'd like to, but again, I'm looking to see if there is a scripting solution. SSIS is probably not an option.
    – webdad3
    May 14, 2013 at 13:36
  • 1
    @JeffV - You could of course cursor through the records, but any DBA would recommend strongly against that approach when you could rather just do one set based statement.
    – RoKa
    May 14, 2013 at 14:23
  • 1
    OK, so in other words, line-by-line is not an option at all. Use a set based statement then like this : insert into #t (...<fields>...) select left(fld, 3) as id, substring(fld, 4, 5) as name ... from mySingleFieldTable
    – RoKa
    May 14, 2013 at 14:28
  • 2
    @JeffV This isn't an issue of "rudimentary" versus "elegant", this is an issue of using the wrong tool for the job. You really should be looking at BCP or SSIS because those tools were designed exactly for tasks like this. Using things like OPENROWSET are cases of fitting the proverbial square peg in a round hole.
    – Mike Fal
    May 14, 2013 at 16:03

3 Answers 3


As mentioned in the comments above, your time would be much better spent using SSIS for this task. Here's a recent video post that discusses handling multi-line record data files much like yours.

Since you're okay with spinning your wheels, let's try to find an answer anyway--if you have the data in a single row, you have somewhere to start. My next step would be to apply a split function. Here is an excellent article about the popular options for such a function. You will need to choose or adapt a function to handle (n)varchar(max) if your files exceed the standard limits. You'll just use your single row of data as the input parameter, and the carriage return as the delimiter.

Once your data is broken into rows, you should be able to liberally apply substring() to slice out your columns for each row type.

This probably won't perform well, but if you need to show a proof of concept to get access to SSIS, it might suffice.

  • Using substring() to split millions of rows is a performance disaster. Take a look at my answer using FORMAT FILE, isn't it a better solution for his situation?
    – Roi Gavish
    May 16, 2013 at 8:01
  • In my very limited experience, SQL isn't as bad at string manipulation as everyone says--this is how I'd try it if SSIS vaporized itself, and if the performance wasn't acceptable, I'd try something else.
    – SQLFox
    May 16, 2013 at 13:27

Your best option (considering your limitations) is probably using OPENROWSET with a FORMAT FILE

Take a look at those links:

Use a Format File to Bulk Import Data
You can use the BCP utility once locally to Create a Format File
I prefer the XML Format Files

  • Not having experience with FORMAT FILE, I've looked into it briefly, but I haven't seen an example that addresses files like the example, where the header has one set of columns, the data rows another, and the footers another. Can you suggest a source for info?
    – SQLFox
    May 16, 2013 at 13:41
  • @SQLFox I will search for the sources that helped me do it, it was a long time ago... I can also write an example for Jeff, I just need a few more lines in his example, and the structure of his destination table.
    – Roi Gavish
    May 16, 2013 at 14:48
  • Jusiticator - my example is very rudimentory. The actual data I have is proprietary so I can't put that here. I'd love to see a working example though.
    – webdad3
    May 22, 2013 at 14:37

Since you are trying to avoid SSIS and BCP: If you don't have a deep-seated hatred of MS Access, you might want to take a brief look at it. It has a pretty slick flat-file import wizard. I prefer it over SSIS for small one-time (manual) jobs with flat-files. MSAccess can link directly into SQL Server (or equiv) tables and import directly into the server (instead of humble MS Access tables).

For bigger jobs or recurring jobs, BCP or SSIS are going to be your best bets.

  • MS-Access uses a row-by-row method when working with SQL Server. For 100's of files with 1000's or more lines that will be devestating.
    – Roi Gavish
    May 16, 2013 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.