Script for replacing one value in many insert queries with one value after the other from a list

Script for replacing one value in many insert queries with one value after the other from a list

1
NewbieNewbie
1

    Apr 27, 2021#1

    I have the following insert statement where two values remain the same, the middle value has about 400 different values, one for each row.

    Code: Select all

    INSERT INTO TableName(ApplicationName, Value, FieldName) VALUES('AppName','Al Cote Console','Terminal')
    I need to have an insert statement for each of the 400 values to replace 'Al Cote Console'. The other values remain the same. The list of values to replace Al Cote Console are in a text file list.
    I'm new to UltraEdit and I know there are better ways to do it, but this will suffice for now.

    Thanks,
    Bruce

    6,676579
    Grand MasterGrand Master
    6,676579

      Apr 27, 2021#2

      Such a task cannot be done with using just the command Replace if the SQL file contains also other lines and not only this statement in one block. An UltraEdit script is needed although it would be also possible to use an SQL script for the same task.

      This script code can be copied and saved into a *.js file and executed with Run active script respectively Play script on being the active file. The first two files in list of opened files (most left on open file tabs bar) must be the two files with the SQL statements to update and the values in a list with each value on a separate line. The script finds out automatically which one of the first two files contains the SQL statements to find and replace the middle value.

      Please note that the replace is a Perl regular expression replace and for that reason each value read from list file is interpreted as regular expression replace string which is important if the values in the list contain one or more backslashes. The script code needs to be updated if the values in list file contain also  one or more \ (or ^ on replacing the replace() by a find() and write()).

      Code: Select all

      // Are at least two files opened?
      if (UltraEdit.document.length > 1)
      {
         var asValuesList;
         var nReplaceCount = 0;
         var nSqlFileIndex = -1;
         var sSearchExpression = "INSERT INTO TableName\\(ApplicationName, Value, FieldName\\) VALUES\\('AppName','\\K[^\\r\\n']*";
      
         // Define environment for this script.
         UltraEdit.insertMode();
         UltraEdit.columnModeOff();
      
         // Define the parameters for the Perl regular expression finds.
         UltraEdit.perlReOn();
         UltraEdit.document[0].findReplace.mode=0;
         UltraEdit.document[0].findReplace.matchCase=true;
         UltraEdit.document[0].findReplace.matchWord=false;
         UltraEdit.document[0].findReplace.regExp=true;
         UltraEdit.document[0].findReplace.searchDown=true;
         UltraEdit.document[0].findReplace.searchInColumn=false;
      
         // Move caret to top of the first file (most left on file tabs bar).
         UltraEdit.document[0].top();
      
         // Is the SQL statement found in first file?
         if (UltraEdit.document[0].findReplace.find(sSearchExpression))
         {
            // Move caret back to beginning of the statement line.
            UltraEdit.document[0].gotoLine(0,1);
            // Select all in the second file.
            UltraEdit.document[1].selectAll();
            // Is the second file not an empty file.
            if (UltraEdit.document[1].isSel())
            {
               // Load all lines with DOS line endings (carriage return + line-feed)
               // as an array of strings with each line being one string in the array.
               asValuesList = UltraEdit.document[1].selection.split("\r\n");
               // Move caret to top of the second file which cancels the selection.
               UltraEdit.document[1].top();
               // Make the second file the active file to avoid document window
               // updates on being a maximized document window while the replaces
               // are run on the first file one after the other in a loop.
               UltraEdit.document[1].setActive();
               // The first file is the file to modify with the SQL statements.
               nSqlFileIndex = 0;
            }
         }
         else
         {  // Move caret to top of the second file.
            UltraEdit.document[1].top();
            UltraEdit.document[1].findReplace.mode=0;
            UltraEdit.document[1].findReplace.matchCase=true;
            UltraEdit.document[1].findReplace.matchWord=false;
            UltraEdit.document[1].findReplace.regExp=true;
            UltraEdit.document[1].findReplace.searchDown=true;
            UltraEdit.document[1].findReplace.searchInColumn=false;
            // Is the SQL statement found in the second file?
            if (UltraEdit.document[1].findReplace.find(sSearchExpression))
            {
               UltraEdit.document[1].gotoLine(0,1);
               UltraEdit.document[0].selectAll();
               if (UltraEdit.document[0].isSel())
               {
                  asValuesList = UltraEdit.document[0].selection.split("\r\n");
                  UltraEdit.document[0].top();
                  UltraEdit.document[0].setActive();
                  nSqlFileIndex = 1;
               }
            }
         }
      
         // Was the SQL statement found in one of the first two files
         // and lines (values) could be loaded from the other file?
         if (nSqlFileIndex >= 0)
         {
            // Remove the last string on being an empty string because of
            // the last line in values list file has also a line ending.
            if (!asValuesList[asValuesList.length-1].length) asValuesList.pop();
      
            // Define the parameters for the replaces in a loop.
            UltraEdit.document[nSqlFileIndex].findReplace.preserveCase=false;
            UltraEdit.document[nSqlFileIndex].findReplace.replaceAll=false;
            UltraEdit.document[nSqlFileIndex].findReplace.replaceInAllOpen=false;
      
            for (var nValueIndex = 0; nValueIndex < asValuesList.length; nValueIndex++)
            {
               // Ignore all other empty string (empty lines) in list file too.
               if (!asValuesList[nValueIndex].length) continue;
               // Replace the middle value in the next found SQL statement.
               if (UltraEdit.document[nSqlFileIndex].findReplace.replace(sSearchExpression,asValuesList[nValueIndex]))
               {
                  nReplaceCount++;
               }
               else
               {
                  break;
               }
            }
      
            // Inform the script user in output window not shown explicitly about
            // the number of statements on which middle value was replaced by a
            // value from the list.
            UltraEdit.outputWindow.write("Updated " + nReplaceCount.toString(10) +
                                         " SQL statement" + ((nReplaceCount==1) ? "" : "s") +
                                         " in the file: " + UltraEdit.document[nSqlFileIndex].path);
            // Search for one more SQL statement and inform the script user in
            // output window displayed automatically if there is indeed one more
            // SQL statement for which there is no more value in the list.
            if (UltraEdit.document[nSqlFileIndex].findReplace.find(sSearchExpression))
            {
               UltraEdit.outputWindow.write("There is at least one more SQL statement, but no more value in the list.");
               UltraEdit.outputWindow.showWindow(true);
            }
      
            // Make the file with the SQL statements the active file.
            UltraEdit.document[nSqlFileIndex].setActive();
         }
      
         if (!nReplaceCount)
         {
            // Inform the user of the script in output window that no replace
            // could be run as either none of the first two files contains
            // the searched SQL statement or the other file is an empty file.
            UltraEdit.outputWindow.write("None of the first two files is modified by the script.");
            UltraEdit.outputWindow.showWindow(true);
         }
      }
      
      Best regards from an UC/UE/UES for Windows user from Austria

      19376
      MasterMaster
      19376

        Apr 28, 2021#3

        Hi,

        I suppose that each value is on a single line. Then you can open a copy of your list file and then do this Perl Replace all:

        F: ^.+$
        R: INSERT INTO TableName(ApplicationName, Value, FieldName) VALUES('AppName','$&','Terminal')

        BR, Fleggy