May 13, 2002 at 11:11 am
I’m trying to write a Stored Procedure(SQL SERVER 7). I have a table(TABLE A) that only has 1 field. I’m trying to loop through the records and write the data to TABLE B. The first few characters(Segments) of data in TABLE A define what the data represents and should be written to the relevant field in TABLE B. When the LIN1 segment repeats, I want to begin writing to the next record in TABLE b. Here is some sample data. Any ideas?
LIN1 BP 64333AA01000RC N001ZZ D-EKANBAN
UNT PC
PID SHADE, PACKAGE TRAY
PO41 00006
PRS 4
REF DKN1
REF LUFF-04
REF MRM390
REF LST2W-29
REF SD
SUB --------------------------SUB-ROUTEITSKOX01
PER SC LEACH, M.-15TE 5018382000
SDP NF
SHP 90CD020513 0100845MA 60160547-6093115
DEL ON2002051401
CD1 DOM020 D120020513 T11110
MRT PLANT1 OS01 UD20020514 UT1025 NC00015
TD5 ----------------------------ROUTINGDEKCM111
LIN1 BP 64333AA01000RC N001ZZ D-EKANBAN
UNT PC
PID SHADE, PACKAGE TRAY
PO41 00006
PRS 4
REF DKN1
REF LUFF-04
REF MRM390
REF LST2W-29
REF SD
SUB --------------------------SUB-ROUTEITSKOX02
PER SC LEACH, M.-15TE 5018382000
SDP NF
SHP 78CD020513 0101645MA 60160548-6093116
DEL ON2002051402
CD1 DOM020 D120020513 T12135
MRT PLANT1 OS02 UD20020514 UT2110 NC00013
TD5 ----------------------------ROUTINGDEKCM105
LIN1 BP 64333AA01000RC N001ZZ D-EKANBAN
May 13, 2002 at 1:10 pm
Looks lke you have 2 options, one is to use a cursor to loop the records and insert when you found the pieces. 2 create a temp table with an IDENTITY column and insert to it then use a WHILE loop and maybe so other fancy ways to deal with it. Can you tell me how these will be inserted into Table B and the structure of the table so I can put my best idea forth?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 13, 2002 at 2:10 pm
TABLE B will contain fields = LEFT(TABLEA.FIELD1, 4). All fields in TABLEB will beVARCHAR(30). I want to write the LIN1 segment in TABLE A to the LIN1 field in TABLE B. THE UNT segement would be written to the same record in TABLE B. I want to continue parsing through the date until I hit the next LIN1 segment which should be written to the 2ND record in TABLE B. I hope that helps.
May 13, 2002 at 5:05 pm
Ok here is the steps I would take to do this quickly and as best as I can think right, efficiently.
Create a temp table simliar to so
CREATE TABLE #tempX (
[unid] [int] IDENTITY(1,1) NOT NULL,
[value] [varchar](8000) NOT NULL,
SAME_COL_DEFS_AS_TABLEB.....
)
You will need a few variables
Then create a cursor to loop thru Table A
each loop do a check for LEFT(col,4) = 'LIN1'
if found then do an insert into the temp table and call @@IDENTITY to get the identity value from this insert into a varibale to hold it. Or use a SELECT max(unid) FROM #tempX to get it since this is the only code working with this temp table.
Next loop if LEFT(col,4) = 'LIN1' is not true you have an else that does and update like so
UPDATE #tempX SET value = value + ISNULL(@yourcursorvaribleforcurrentdata,'') WHERE unid = @yourvaribalefounidvalue
So as you go thru now you have seperated and concatinated the values for each together in single rows. When doen with cursor close it and deallocate.
Now to finish into TableB do a while loop that starts at one and goes to the last value of unid from the temp table.
Each go pull out a variable and using CHARINDEX find each ' ' or empty space and a WHILE loop checking for ' ' and fill in the proper fields until you run out out of data from value which should be you last field so update it's field. Each is an update to the proper column relative to TableB in the temp table.
Once done, then you do a INSERT INTO tableB (colnames) SELECT relativecolnames FROM #tempX
to move them to tableB. Sorry this is text and no example but it is storming here and my test machine is not on a surge protector so it is down right now. The logic should be right and hopefully you can follow what I was doing. If not let me know and I will try to get you an example.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply