MS SQL Server - Sql Stored Procedue Help

Author

Sql Stored Procedue Help

msnews

2005-10-20, 8:23 pm

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

Quote:
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_stri
ng,user from table where
comma_delimited_stri
ng <> '' ";
ROWRESULT=EXEC($sql)
;
while(ROWRESULT IS NOT NULL)
{
$user=ROWRESULT['user'];
string=ROWRESULT& #91;'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?

John Bell

2005-10-20, 8:23 pm

Hi

If you can get a ODBC or OLEDB connection from your SQL Server , you may
want to look at using DTS (see books online and www.sqldts.com/) for this
data transfer. Otherwise check out
http://www.sommarskog.se/arrays-in-sql.html for possible solutions.

John

"msnews" wrote:

> 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
>
> Quote:
> 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_stri
ng,user from table where
> comma_delimited_stri
ng <> '' ";
> ROWRESULT=EXEC($sql)
;
> while(ROWRESULT IS NOT NULL)
> {
> $user=ROWRESULT['user'];
> string=ROWRESULT& #91;'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?
>
>
>

Sponsored Links