Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    109

    Unanswered: Help in SQL procedure

    I am writing a upgrader utility in php/mssql and have a problem in handling large amount of data.
    The utility selects comma delimited string from a huge table, splits it into an array and inserts it as separate
    records in to an another table. But this utility seems to run forever and is not an ideal solution for us.
    Would it be possible to achieve the same using tsql stored procedures?


    An over simplified example can be
    table
    1 group1,group2,group3
    2 group2,group3, group4


    table1
    id user group
    1 1 group1
    2 1 group2
    3 1 group3
    4 2 group2
    5 2 group3
    6 2 group4
    and so on.


    I have attached the pseudo code herewith:

    Code:
    $sql="select comma_delimited_string,user from table where comma_delimited_string <> '' ";
    ROWRESULT=EXEC($sql);
    while(ROWRESULT IS NOT NULL)
    {
    	$user=ROWRESULT['user'];
    	string=ROWRESULT['comma_delimited_string'];
    	$IDs=SPLIT(string,",");
    	for($i=0;$i<count($IDs);$i++)
    	{
    		$id=$IDs[$i];
    		$sql="select ID from table1 where id='$id' and user='$user' ";
    		ROWRESULT2= EXEC($sql);
    		if(ROWRESULT2 IS NOT NULL)
    		{
    			$sql="insert into table1 values ('$id','$user') ";
    			EXEC($sql);
    		
    		}	
    		ROWRESULT2 NEXT;	
    	}	
    ROWRESULT NEXT;	
    	
    }
    table has more than 2 lakhs records and hence php is not able to handle it fastly. I guess we can do it using
    stored procedures but am not sure how to do the split in tsql. Can anyone help me here? I would want the utility to take not more than few seconds. What should my approach be here?
    Last edited by rexselin; 10-19-05 at 03:04. Reason: error in attached code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •