[SOLVED] Multiple update sql passthrough

Hello,

i have to update a ms sql server table. i will get the new and old values in a txt file and can point a master file on it.
Aprox i will get 50 update statements.

Old_ID New_ID
123 456
788 555
789 555
... ...

How can i put these values into &variables to use them
in a loop in the sql passthrouht statement? And how can
i tell the loop to take the next update row?

SET SQLENGINE = SQLMSS
SQL SET SERVER BLABLA
-RUN
SQL UPDATE BLABLA.dbo.BLABLA
  SET ID = '&New_ID'
WHERE ID = '&Old_ID';
END
-RUN

Regards

Christian

This message has been edited. Last edited by: ChristianP,

WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT

Posts: 156 | Location: Essen Germany | Registered: December 02, 2010

posted

Try this (the transfile.ftm can also be created by a WebFOCUS table request of course)

-Fred-

FILEDEF transfile DISK transfile.ftm
-RUN
-WRITE transfile 123 456
-WRITE transfile 788 555
-WRITE transfile 789 555
-RUN

-SET &NUM_LINES = 3;

-REPEAT :LB_READ FOR &I FROM 1 TO &NUM_LINES;
-READ transfile &ID_OLD.&I.I3 &FILLER.A1 &ID_NEW.&I.I3
-:LB_READ

-REPEAT :LB_UPDATE FOR &I FROM 1 TO &NUM_LINES;
SQL UPDATE BLABLA.dbo.BLABLA
  SET ID = '&ID_NEW.&I'
WHERE ID = '&ID_OLD.&I';
END
-RUN
-:LB_UPDATE

Waz

Expert

posted

Hide Post

If its just an UPDATE, the you could also use MODIFY. Just need to have a MASTER for your SQL source.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!