Having major problems with my insert query logic

I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.

Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.

Here is some example code from my program:
sub Test{
foreach my $table(keys %$HoH){
foreach my $field(keys %{$HoH->{$table}}){
if($table eq "CPU"){
my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values ('$field',
'$HoH->{CPU}{$field}{Name}',
'$HoH->{CPU}{$field}{MaxClockSpeed}' ,
'$HoH->{Host}{SystemNetName}')";
print "$CPUstatement\n";
if ($db->Sql($CPUstatement))
{
print "Error on SQL Statement\n";
Win32::ODBC::DumpError();
}
else
{
print "successful\n";
}
}
}


}
}

Thanks,
LauraI'm assuming that your hash values are printing as expected. The construction looks strange (but it could be fine) to me.

Is the CPUNumber the primary key for the CPU table? If so, you could use something like:my $CPUstatement = "IF EXISTS (SELECT * FROM CPU WHERE CPUNUMBER = '$field')
THEN UPDATE CPU
SET Name = '$HoH->{CPU}{$field}{Name}'
, MaxClockSpeed = '$HoH->{CPU}{$field}{MaxClockSpeed}'
, SystemNetName = '$HoH->{Host}{SystemNetName}'
WHERE CPUNumber = '$field'
ELSE INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values (
'$field'
, '$HoH->{CPU}{$field}{Name}'
, '$HoH->{CPU}{$field}{MaxClockSpeed}'
, '$HoH->{Host}{SystemNetName}')";-PatP|||I tried your code and I am getting an error -> Incorrect Systax near the keyword 'THEN'

What do you suppose that means? I copied and pasted the code as is.

Thanks,
Laura|||That error means that I don't proofread very well ;) I was composing as I typed, and simply got ahead of myself then didn't clean up afterwards. Just remove the word THEN from that statement. Sorry.

-PatP|||oh wow. That's so cool, it worked.

Thanks Pat for your help. I learn new things everyday.

-Laura|||I just love it when I can make a lovely lady happy!

-PatP

Hardware requirements

We're running an SQL 2000 server (Standard Edition) that is running
particularly slowly on it's existing "low end" hardware.
We have want to purchase a faster machine for it and have had two
near-identical quotes for:
A - Twin XEON 3.8 GHz processors
B - Twin XEON dual core 2.8 GHz processors (so effectively a quad processor)
What hardware would benefit SQLServer 2000 most?
Thanks for any advice
GriffAdditional information
This SQL Server services a single application (single user), rather than a
multi-connection scenairo. Some of the queries take literally hours to run
on the old hardware.
Griff|||> This SQL Server services a single application (single user), rather than a
> multi-connection scenairo.
Multiple cores are most useful for multiple concurrent queries and parallel
plans, including index rebuilds. If you are focusing on improving
performance of a single concurrent query that doesn't involve parallelism,
faster CPUs are more beneficial than more cores.

> Some of the queries take literally hours to run on the old hardware.
Be sure you know where your bottleneck is before you throw hardware at the
problem. It is often the case that much improvement can be made from schema
and application tuning. Once the app is well tuned, you can identify the
hardware bottleneck and address accordingly. Faster processors might not
help much if you have a disk bottleneck.
Hope this helps.
Dan Guzman
SQL Server MVP
"Griff" <howling@.the.moon> wrote in message
news:%233kEehuMGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Additional information
> This SQL Server services a single application (single user), rather than a
> multi-connection scenairo. Some of the queries take literally hours to
> run on the old hardware.
> Griff
>

Hardware requirements

We're running an SQL 2000 server (Standard Edition) that is running
particularly slowly on it's existing "low end" hardware.
We have want to purchase a faster machine for it and have had two
near-identical quotes for:
A - Twin XEON 3.8 GHz processors
B - Twin XEON dual core 2.8 GHz processors (so effectively a quad processor)
What hardware would benefit SQLServer 2000 most?
Thanks for any advice
Griff
Additional information
This SQL Server services a single application (single user), rather than a
multi-connection scenairo. Some of the queries take literally hours to run
on the old hardware.
Griff
|||> This SQL Server services a single application (single user), rather than a
> multi-connection scenairo.
Multiple cores are most useful for multiple concurrent queries and parallel
plans, including index rebuilds. If you are focusing on improving
performance of a single concurrent query that doesn't involve parallelism,
faster CPUs are more beneficial than more cores.

> Some of the queries take literally hours to run on the old hardware.
Be sure you know where your bottleneck is before you throw hardware at the
problem. It is often the case that much improvement can be made from schema
and application tuning. Once the app is well tuned, you can identify the
hardware bottleneck and address accordingly. Faster processors might not
help much if you have a disk bottleneck.
Hope this helps.
Dan Guzman
SQL Server MVP
"Griff" <howling@.the.moon> wrote in message
news:%233kEehuMGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Additional information
> This SQL Server services a single application (single user), rather than a
> multi-connection scenairo. Some of the queries take literally hours to
> run on the old hardware.
> Griff
>

Hardware requirements

We're running an SQL 2000 server (Standard Edition) that is running
particularly slowly on it's existing "low end" hardware.
We have want to purchase a faster machine for it and have had two
near-identical quotes for:
A - Twin XEON 3.8 GHz processors
B - Twin XEON dual core 2.8 GHz processors (so effectively a quad processor)
What hardware would benefit SQLServer 2000 most?
Thanks for any advice
GriffAdditional information
This SQL Server services a single application (single user), rather than a
multi-connection scenairo. Some of the queries take literally hours to run
on the old hardware.
Griff|||> This SQL Server services a single application (single user), rather than a
> multi-connection scenairo.
Multiple cores are most useful for multiple concurrent queries and parallel
plans, including index rebuilds. If you are focusing on improving
performance of a single concurrent query that doesn't involve parallelism,
faster CPUs are more beneficial than more cores.
> Some of the queries take literally hours to run on the old hardware.
Be sure you know where your bottleneck is before you throw hardware at the
problem. It is often the case that much improvement can be made from schema
and application tuning. Once the app is well tuned, you can identify the
hardware bottleneck and address accordingly. Faster processors might not
help much if you have a disk bottleneck.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Griff" <howling@.the.moon> wrote in message
news:%233kEehuMGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Additional information
> This SQL Server services a single application (single user), rather than a
> multi-connection scenairo. Some of the queries take literally hours to
> run on the old hardware.
> Griff
>