title: Someone have an example how do a CLR Insert in SQL Server 2008?
body: <p>I'm new here and English isn't my native language, so I hope you guys can understand me.</p>
  
  <p>Can someone give me an example of how to make a function that create a table with data from result of nested loops.</p>
  
  <p>I'm trying do a SQL CLR function in C# for create a table with all combinations for a lottery game, I have the following pascal code:</p>
  
  <pre><code>program lotery;
  uses fdelay, crt;
  var
     x,y,z,a,b,c:integer;
     cont:longint;
  begin
       clrscr;
       x:=0;
       y:=0;
       z:=0;
       a:=0;
       b:=0;
       c:=0;
       cont:=0;
       for x:=0 to 59 do
           begin
                for y:=x+1 to 59 do
                begin
                     for z:=y+1 to 59 do
                     begin
                          for a:=z+1 to 59 do
                          begin
                               for b:=a+1 to 59 do
                               begin
                                    for c:=b+1 to 59 do
                                    begin
                                         cont:=cont+1;
                 Writeln ('Dezenas: ',x,' - ',y,' - ',z,' - ',a,' - ',b,' - ',c,' = Total de Dezenas: ',cont);
                                    end;
                               end;
                          end;
                     end;
                end;
           end;
  readkey;
  end.
  </code></pre>
  
  <p>Using only the T-SQL I noticed that I cant do nested loops with a <code>while</code> inside of another <code>while</code>. So I'm trying do a SQL CLR function, but I have no idea for where start.</p>
  
  <p>I'm using SQL Server 2008 and Visual Studio 2010.</p>
  
  <p>I'm reading the MSDN examples, but mostly of they don't work and are so confusing.</p>
  
  <p>Addendum 08/29/2011</p>
  
  <p>I'm learning how this site works too now, so sorry if I cant do an addendum on the question, I only see the edit option in the main question</p>
  
  <p>I have a working C++ (this one I compile using DEV C++) code making a txt with all combination, heres my code:</p>
  
  <pre><code>#include &lt;iostream&gt;
  #include &lt;fstream&gt;
  
  using namespace std;
  
  int main()
      {
  
          ofstream myfile;
          myfile.open ("Combinacoes.txt");
  
          int total = 0;
  
          for (int contador = 1; contador &lt;= 60; contador++)
              for (int Bola_01 = 1; Bola_01 &lt;= 60; Bola_01++)
                  for (int Bola_02 = 1; Bola_02 &lt;= 60; Bola_02++)
                      for (int Bola_03 = 1; Bola_03 &lt;= 60; Bola_03++)
                          for (int Bola_04 = 1; Bola_04 &lt;= 60; Bola_04++)
                              for (int Bola_05 = 1; Bola_05 &lt;= 60; Bola_05++)
                                  for (int Bola_06 = 1; Bola_06 &lt;= 60; Bola_06++)
                                      {
                                      total = Bola_01 + Bola_02 + Bola_03 + Bola_04 + Bola_05 + Bola_06;
                                      myfile &lt;&lt; Bola_01 &lt;&lt; "," &lt;&lt; Bola_02 &lt;&lt; "," &lt;&lt; Bola_03 &lt;&lt; "," &lt;&lt; Bola_04 &lt;&lt; "," &lt;&lt; Bola_05 &lt;&lt; "," &lt;&lt; Bola_06 &lt;&lt; "," &lt;&lt; total &lt;&lt; endl;
  //                                  printf ("%d , %d , %d , %d , %d , %d , %d \n", Bola_01, Bola_02, Bola_03, Bola_04, Bola_05, Bola_06, total);
                                      total = 0;
                                      }
          return 0;
      }
  </code></pre>
  
  <p>Then I can import this data for SQL using DTS, my objective is have a CLR for do all job in the SQL and can shrink the table of combinations for fast upload the database and rebuild fast using an function calling the C++ code as DLL (or in other words using the CLR).
  Another point is I will work on this combinations table doing many searches (The Selects I will do using the TSQL and sending the result for an array inside C#), and offcourse I have all results of the lottery for search data too, in both cases (combinations and resuts) I will need to use arrays to do my research, this is another point for  work with CLR, I remember many things from C++ from when I was in the university, and in my oppinion is pretty easy and fast work with C# instead use TSQL Cursors for my goal.
  My idea is do SQL database with C# and TSQL Procedures to give me the best and fastest hints to play in the lottery spending around US$ 50,00 per week, the first book I'm reading is:</p>
  
  <p>How to win in the lottery by Gail Howard</p>
  
  <p>Dunno the right name in english.</p>
  
  <p>I will use many other system from blogs, and websites too, but only the ones i can understand, for me, there no point make a program using complex formulas where i cant understand.</p>
  
  <p>Thanks for all answers, and I will keep trying get the CLR working for me instead need use DTS for export and import data every time, and thanks so much Martin Smith for show me how is possible simulate a nested while loop inside of the MSSQL, I got a problem with memory like (my laptop have the MSSQL using 2048 MB free RAM to SQL):</p>
  
  <blockquote>
    <p>An error occurred while executing batch. Error message is: Exceção do tipo 'System.OutOfMemoryException' foi acionada.</p>
  </blockquote>
  
  <p>But before the error was working pretty nice, and I'm pretty sure if an question in Microsoft SQL exams ask if a nested loop is possible appear, I will answer with your example ;-p</p>