Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
piya
Starting Member
1 Post |
 Posted - 2011-07-18 : 12:57:15
|
| Hi, I need huge help. I am struggling since so long to get the solution for my problem.I have one text file(EDI file). I want to load it into sql table, based on certain condition. The part of the work I have done, I am able to bulk insert the text file into the temp table. Now I want to load the data from temp table to the original table with certain conditions.Like :in the bulk inserted filed of the temp table I have separated out the filed based on raw terminator now I wantif the first three starting character are "REF" in the string then next filed after the * should go into one column of the original table and then 3rd should go to another column and so on until the raw terminator.~BGN*00*500307680120110708C001*20110709*0404****2~~REF*38*5003076801~~N1*P5*INDIANA HEALTH COVERAGE PROGRAM*ZZ*IHCP~Can anybody help me in this. I will really appreciate your help. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-18 : 17:58:38
|
| You can try to do it with charindex to parse the data into multiple columns. However, I think that would be too hard to do if you have a lot of columns that you want to parse.An easier way would be to use a splitter function to split each row the way you want it, and then use that to populate the table. There are several splitter functions available, for example in Fig. 21 in here So the steps would be:1. Split the rows and put into a new temporary or virtual table.2. Use the data from the temp table to populate your final destinations.From your description it is hard to write code for someone who is not familiar with your database and tables. If you can post some sample data and DDLs for the tables, I am sure someone on the forum will be able to help more than offer vague suggestions like I did. Brett's blog might help |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Spilt column in Sql 2008