Database

PersonID Name   Age
1        John   32    
2        Kendra 20
3        Steve  29
...

public ArrayList RetriveStdentInfo()
   {
       SqlCommand cmd = new SqlCommand();
       cmd.Connection = myCon;
       cmd.CommandText = "dbo.StoredProcedure2";
       //cmd.Parameters=ParameterDirection.ReturnValue;
       cmd.CommandType = CommandType.StoredProcedure;
       myCon.Open();
       IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
       ArrayList seqCat = new ArrayList();
       ArrayList seqStudent = new ArrayList();
       <code>ArrayList destnation = new ArrayList();</code>
       int prevCatId = 0;
       while (reader.Read())
       {

           MyStudent std = new MyStudent();
           std.Stid = reader.GetInt32(0);
           std.Sname = reader.GetString(reader.GetOrdinal("stname"));
           std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
           if (prevCatId != reader.GetInt32(reader.GetOrdinal("Catgid")))
           {
               CatgBL cat = new CatgBL();
               cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));
               seqCat.Add(cat);
               prevCatId = reader.GetInt32(reader.GetOrdinal("Catgid"));
           }
           seqStudent.Add(std);

       }

    <code>   foreach ( MyStudent var in seqStudent)
       {
           destnation.Add((int)var.Catgid);
           destnation.Add((string)var.Sname);
           destnation.Add((int)var.Stid);
       }
       foreach (CatgBL var in seqCat)
       {
           destnation.Add((int)var.Cid);
           destnation.Add((string)var.CatgName);
       }
        return destnation;</code>
   }

INSERT INTO TargetTable (Title)
SELECT Title
FROM SourceTable
--
UPDATE TargetTable 
SET ParentID = (SELECT tt1.Id 
                FROM TargetTable tt1, SourceTable st1, SourceTable st2
                WHERE targettable.Title = st1.Title
                AND st2.Id = st1.ParentId
                AND st2.Title = tt1.Title)

Maysam Mahfouzi wrote:

INT in destination table

Maysam Mahfouzi wrote:

Titles may not necessarily be unique

Maysam Mahfouzi wrote:

Could you suggest a good solution please?

PIEBALDconsult wrote:

That's not what this says:

ID SMALLINT
ParentID INT

PIEBALDconsult wrote:

That would seem to eliminate the other suggestion.

Maysam Mahfouzi wrote:

I can't rely on old VARCHAR values to calculate new ID

UPDATE NewTable 
SET ParentId=B.ID
FROM NewTable A
INNER JOIN NewTable B
ON A.OldParentID=B.OldID

INSERT INTO NewTable (OldID,OldParentID,Description) SELECT * FROM OldTable
SELECT * FROM NewTable
 
UPDATE A
SET ParentID=B.ID
FROM NewTable A
INNER JOIN NewTable B
ON A.OldParentID=B.OldID
 
SELECT * FROM NewTable

1	NULL	1	NULL	Section 1
2	NULL	2	NULL	Section 2
3	1	1.a	1	Section 1.a
4	1	1.b	1	Section 1.b
5	2	2.a	2	Section 2.a
6	2	2.b	2	Section 2.b
7	3	1.a.i	1.a	Section 1.a.i
8	3	1.a.ii	1.a	Section 1.a.ii