How to change table numbering scheme at a new section?

The following solution does not support hyperref since it re-uses counters associated with supplemental tables (or figures).

\documentclass{article}
\usepackage{titlesec}% http://ctan.org/pkg/titlesec
\newcounter{mytable}\newcounter{myfigure}
\titleformat{\section}% <command>
  [hang]% <shape>
  {\setcounter{mytable}{\value{table}}\setcounter{myfigure}{\value{figure}}% Store table/figure counters
   \setcounter{table}{0}\global\def\thetable{S\arabic{table}}%
   \setcounter{figure}{0}\global\def\thefigure{S\arabic{figure}}%
   \normalfont\Large\bfseries}% <format>
  {\setcounter{table}{\value{mytable}}\setcounter{figure}{\value{myfigure}}% Restore table/figure counters
   \global\def\thetable{\arabic{table}}%
   \global\def\thefigure{\arabic{figure}}%
   \thesection}% <label>
  {2.3ex plus.2ex}% <sep>
  {}% <before code>
  %[]% <after code>
\begin{document}

\tableofcontents
\listoftables

\section{Start}
Start text

\section{Middle}
Middle text
\begin{table}[ht]
    \centering STUFF
\caption{A normal table}
\end{table}

\begin{table}[ht]
    \centering STUFF
\caption{A normal table}
\end{table}

\section*{Supplemental}

\begin{table}[ht]
    \centering SUPPLEMENTAL STUFF
\caption{A supplemental table}
\end{table}

\begin{table}[ht]
    \centering SUPPLEMENTAL STUFF
\caption{A supplemental table}
\end{table}

\section{Last}
End text
\begin{table}[ht]
    \centering STUFF
\caption{A normal table}
\end{table}

\begin{table}[ht]
    \centering STUFF
\caption{A normal table}
\end{table}

\section*{Supplemental}

\begin{table}[ht]
    \centering SUPPLEMENTAL STUFF
\caption{A supplemental table}
\end{table}

\begin{table}[ht]
    \centering SUPPLEMENTAL STUFF
\caption{A supplemental table}
\end{table}

\end{document}

I've used titlesec to hook into the sectional unit \section that conditionally executes certain parts of typesetting the heading (the settings are kept similar to the original article formatting). The label and separator part of the heading is only called when using \section, and therefore not "called" or used in \section*.

The concept is based on storing the current value of the table (and figure) counter in mytable (myfigure), after which it is set to 0. Then, only if you're using \section its value is restored. At both instances the display of the counter is renewed to prepend S or not.

If I understand your objective correctly, you're aiming to number the tables (and figures?) as plain integers starting with 1 in the "main" body of the text, and to restart the numbering at 1, but now with an "S" prefixed, once you're in the supplemental part of the document.

You don't indicate if you've already defined a special command to make some modifications to the layout and other aspects of the document's appearance, so I'll assume you haven't done so. A relatively clean way to proceed would be to define a new command, say \startsupplement, in the preamble and to invoke this command at the start of that part of the document. This new command could be defined as follows:

\newcommand\startsupplement{%
    \makeatletter 
       \setcounter{table}{0}
       \renewcommand{\thetable}{S\arabic\c@table}
       \setcounter{figure}{0}
       \renewcommand{\thefigure}{S\@arabic\c@figure}
    \makeatother}