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.
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?