Someone have an example how do a CLR Insert in SQL Server 2008?

I'm new here and English isn't my native language, so I hope you guys can understand me.

Can someone give me an example of how to make a function that create a table with data from result of nested loops.

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:

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.

Using only the T-SQL I noticed that I cant do nested loops with a while inside of another while. So I'm trying do a SQL CLR function, but I have no idea for where start.

I'm using SQL Server 2008 and Visual Studio 2010.

I'm reading the MSDN examples, but mostly of they don't work and are so confusing.

Addendum 08/29/2011

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

I have a working C++ (this one I compile using DEV C++) code making a txt with all combination, heres my code:

#include <iostream>
#include <fstream>

using namespace std;

int main()
    {

       开发者_如何学C ofstream myfile;
        myfile.open ("Combinacoes.txt");

        int total = 0;

        for (int contador = 1; contador <= 60; contador++)
            for (int Bola_01 = 1; Bola_01 <= 60; Bola_01++)
                for (int Bola_02 = 1; Bola_02 <= 60; Bola_02++)
                    for (int Bola_03 = 1; Bola_03 <= 60; Bola_03++)
                        for (int Bola_04 = 1; Bola_04 <= 60; Bola_04++)
                            for (int Bola_05 = 1; Bola_05 <= 60; Bola_05++)
                                for (int Bola_06 = 1; Bola_06 <= 60; Bola_06++)
                                    {
                                    total = Bola_01 + Bola_02 + Bola_03 + Bola_04 + Bola_05 + Bola_06;
                                    myfile << Bola_01 << "," << Bola_02 << "," << Bola_03 << "," << Bola_04 << "," << Bola_05 << "," << Bola_06 << "," << total << 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;
    }

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:

How to win in the lottery by Gail Howard

Dunno the right name in english.

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.

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):

An error occurred while executing batch. Error message is: Exceção do tipo 'System.OutOfMemoryException' foi acionada.

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

---

Just to briefly address the question in the title there is an example of using a CLR procedure to do an INSERT on MSDN here.

However having read the motivation for this specific question just to point out firstly that you can nest WHILE loops in TSQL.

This would not an efficient way of achieving your objective though. You need a numbers table with 59 numbers then join it 6 times to do it in a set based way.

Assuming each ball can only appear once and order is not important then

/*Table to Hold the Number Combinations*/
CREATE TABLE dbo.LotteryNumbers
    (
      Number1 INT NOT NULL ,
      Number2 INT NOT NULL ,
      Number3 INT NOT NULL ,
      Number4 INT NOT NULL ,
      Number5 INT NOT NULL ,
      Number6 INT NOT NULL ,
      CONSTRAINT PK_LotteryNumbers PRIMARY KEY CLUSTERED
        ( Number1, Number2, Number3, Number4, Number5, Number6 )
    ) 


/*Create helper table to hold the ball numbers*/
DECLARE @Balls TABLE ( N INT PRIMARY KEY )

 ;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b)

    INSERT  INTO @Balls( N )
            SELECT TOP 59
                    ROW_NUMBER() OVER ( ORDER BY N )
            FROM    E08

  /*Populate the table of lottery numbers*/
INSERT  INTO dbo.LotteryNumbers
        SELECT  T1.N ,
                T2.N ,
                T3.N ,
                T4.N ,
                T5.N ,
                T6.N
        FROM    @Balls T1
                JOIN @Balls T2 ON T1.N < T2.N
                JOIN @Balls T3 ON T2.N < T3.N
                JOIN @Balls T4 ON T3.N < T4.N
                JOIN @Balls T5 ON T4.N < T5.N
                JOIN @Balls T6 ON T5.N < T6.N

NB: If you look at the execution plan for this you will see that SQL Server does effectively write a nested loops program for you.