Database

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);            
   sequence.AddRange(seqStudent);            
   sequence.AddRange(seqCat);        
}

StudentDAL std = new StudentDAL();
     DataGrid1.DataSource = std.RetriveStdentInfo();
      DataGrid1.DataBind();

sequence.AddRange(seqStudent);
sequence.AddRange(seqCat);

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