Timeline for Fixed length text file insert into SQL table
Current License: CC BY-SA 3.0
19 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 16, 2013 at 14:50 | comment | added | Roi Gavish |
@JeffV Please add a few more lines to your data example and the structure of your destination table. I will try to create a sample FORMAT FILE for You.
|
|
| May 15, 2013 at 14:17 | answer | added | TimG | timeline score: 1 | |
| May 15, 2013 at 13:59 | answer | added | SQLFox | timeline score: 3 | |
| May 15, 2013 at 13:34 | answer | added | Roi Gavish | timeline score: 2 | |
| May 14, 2013 at 16:48 | comment | added | webdad3 | @MikeFal that is a fair comment and I agree. It is just the constraint that I'm in right now. | |
| May 14, 2013 at 16:03 | comment | added | Mike Fal |
@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.
|
|
| May 14, 2013 at 14:28 | comment | added | RoKa |
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
|
|
| May 14, 2013 at 14:24 | comment | added | webdad3 | agreed - a cursor is not the way I want to go either. | |
| May 14, 2013 at 14:23 | comment | added | RoKa | @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. | |
| May 14, 2013 at 14:15 | comment | added | webdad3 | is there anyway to do it line by line into a temp table? And then do that as you suggested? | |
| May 14, 2013 at 14:10 | comment | added | RoKa |
Well, you could of course insert into a single field, and then split the text with a load of substrings in a select? For e.g.: select left(fld, 3) as id, substring(fld, 4, 5) as name ...
|
|
| May 14, 2013 at 14:07 | history | tweeted | twitter.com/#!/StackDBAs/status/334308951447445504 | ||
| May 14, 2013 at 13:53 | comment | added | webdad3 | @RoKa I think SSIS is the right way to go! But I need to show a rudementary SQL way of doing it before I can do a more elegant/advanced way. Does that make sense? | |
| May 14, 2013 at 13:43 | comment | added | RoKa |
@JeffV - Could you elaborate as to why SSIS would not be an option? Scripting, such as with OPENROWSET into temp tables and then manipulating the contents, would require an excessive amount of effort to implement and maintain, and therefore would be near impossible to sustain.
|
|
| May 14, 2013 at 13:36 | comment | added | webdad3 | I'd like to, but again, I'm looking to see if there is a scripting solution. SSIS is probably not an option. | |
| May 14, 2013 at 13:35 | comment | added | Kin Shah | How about using SSIS ? If you are going to import the files frequently, SSIS would be the best solution to put in place. | |
| May 14, 2013 at 13:21 | review | First posts | |||
| May 14, 2013 at 13:55 | |||||
| May 14, 2013 at 13:19 | history | edited | RoKa |
edited tags
|
|
| May 14, 2013 at 13:06 | history | asked | webdad3 | CC BY-SA 3.0 |