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,

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

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

Hi FreSte,

perfect solution!!! Works great!!!


Christian