Sending Data From SQL 2005 To And From C# Code

Hi

Background: I am a post-graduate student developing a model of the way HIV/AIDS spreads in communities. The data sets I am using are large (eg census data - 50m records) and the manipulation is complex (eg deciding which individuals will begin a sexual partnership, and with whom).

Problem: I need to process a table of data in code written in C# that will insert the results which are also a table of data into a table in the database.

In the example set up below, a table with rows comprising an Integer and a Real is the input. The output is a table where each row also comprises an Integer and a Real. Each row of input, say (Inti, Reali) leads to Inti rows of output. The example below gives a simple example of this.

The T-SQL code below sets up the problem, and includes simplest possible T-SQL that would lead to the requisite Output, so that you could compare your solution's code's with it:

-- Set up tables to use in example

CREATE TABLE InputTable

(

Int1 INT NOT NULL,

Real1 REAL NOT NULL

)

GO

CREATE TABLE OutputTable

(

Int1 INT NOT NULL,

Real1 REAL NOT NULL

)

GO

--Initialise the Input Table

INSERT INTO InputTable (Int1, Real1) VALUES (3, 0.5)

INSERT INTO InputTable (Int1, Real1) VALUES (5, 0.99)



--Manually put Data into Output table

--I do know how to code this in T-SQL using cursors and loops

--I wish to code this part in CLR

INSERT INTO OutputTable (Int1, Real1) VALUES (1, 0.5)

INSERT INTO OutputTable (Int1, Real1) VALUES (2, 0.5)

INSERT INTO OutputTable (Int1, Real1) VALUES (3, 0.5)

INSERT INTO OutputTable (Int1, Real1) VALUES (1, 0.99)

INSERT INTO OutputTable (Int1, Real1) VALUES (2, 0.99)

INSERT INTO OutputTable (Int1, Real1) VALUES (3, 0.99)

INSERT INTO OutputTable (Int1, Real1) VALUES (4, 0.99)

INSERT INTO OutputTable (Int1, Real1) VALUES (5, 0.99)

--This should show the output

SELECT * FROM InputTable

SELECT * FROM OutputTable

--The output should be

Int1Real1

3 0.5

5 0.99

Int1Real1

1 0.5

2 0.5

3 0.5

1 0.99

2 0.99

3 0.99

4 0.99

5 0.99

Additional Information: I would be grateful if your solution could indicate

* whether the code to be written is a class, stored procedure or user-defined function

* the answer could be included in entirity begining with "using system..."

* example T-SQL that can be used to execute the code

Current knowledge:I can write T-Sql. I have written C# user defined functions and stored procedures, assembled them and run them from within SQL-Server 2005.

Thanks in advance.

Greg