Hot to write this SP

[planz]

Hey all.. Sorry I have never written a SP in my life.
so this would be a newbie question for sure.
Using sql server 7.

Here is my table field setup with sample values all fields are numeric
except date

weekending size effic dailycap workdays planutil demand
variance
5/172002 8 80 85 5 244
102 142

A file with source data will be made available in comma delimitted format to
supply a new weekending value, size and demand.

What I will need is first the dts to bring that in. then I am assuming a
stored procedure to be run (this is why I am here)
to add the data from the comma del. file into the table. If the data EXISTS
I would want it to UPDATE the values that are in the dest table but run a
calculation first which would be the planutil minus demand then the result to
be updated intot the record.
if the record from the tab del. file does not exisst in the dest table then
insert it.


another words the logic i have in mind
read the data from the temp tample (where the file gets imported into)
see if the record exists in the live table is it does update it with the calculation of planutil minus demand
if not create the new record in the live table.
I need it to compare..

Someone help me with some code
i thank you kindly in advance

p.s. And good books dedicated to stored procedures??

[Shefali Naik]

Hi Planz,

I have one question, How is the uniqueness of the record decided.
In other words, based on which field(s) do you check if the record already exists.

Regards,
Shefali


------------
planz at 5/9/2002 11:38:09 AM



Hey all.. Sorry I have never written a SP in my life.
so this would be a newbie question for sure.
Using sql server 7.

Here is my table field setup with sample values all fields are numeric
except date

weekending size effic dailycap workdays planutil demand
variance
5/172002 8 80 85 5 244
102 142

A file with source data will be made available in comma delimitted format to
supply a new weekending value, size and demand.

What I will need is first the dts to bring that in. then I am assuming a
stored procedure to be run (this is why I am here)
to add the data from the comma del. file into the table. If the data EXISTS
I would want it to UPDATE the values that are in the dest table but run a
calculation first which would be the planutil minus demand then the result to
be updated intot the record.
if the record from the tab del. file does not exisst in the dest table then
insert it.


another words the logic i have in mind
read the data from the temp tample (where the file gets imported into)
see if the record exists in the live table is it does update it with the calculation of planutil minus demand
if not create the new record in the live table.
I need it to compare..

Someone help me with some code
i thank you kindly in advance

p.s. And good books dedicated to stored procedures??

[planz]

Hi- sorry about that
'weekending' and 'size' would be unique

------------
Shefali Naik at 5/9/2002 1:25:47 PM

Hi Planz,

I have one question, How is the uniqueness of the record decided.
In other words, based on which field(s) do you check if the record already exists.

Regards,
Shefali


------------
planz at 5/9/2002 11:38:09 AM



Hey all.. Sorry I have never written a SP in my life.
so this would be a newbie question for sure.
Using sql server 7.

Here is my table field setup with sample values all fields are numeric
except date

weekending size effic dailycap workdays planutil demand
variance
5/172002 8 80 85 5 244
102 142

A file with source data will be made available in comma delimitted format to
supply a new weekending value, size and demand.

What I will need is first the dts to bring that in. then I am assuming a
stored procedure to be run (this is why I am here)
to add the data from the comma del. file into the table. If the data EXISTS
I would want it to UPDATE the values that are in the dest table but run a
calculation first which would be the planutil minus demand then the result to
be updated intot the record.
if the record from the tab del. file does not exisst in the dest table then
insert it.


another words the logic i have in mind
read the data from the temp tample (where the file gets imported into)
see if the record exists in the live table is it does update it with the calculation of planutil minus demand
if not create the new record in the live table.
I need it to compare..

Someone help me with some code
i thank you kindly in advance

p.s. And good books dedicated to stored procedures??

[Shefali Naik]

Hi Planz,

Here's the code.
The table tmpplans , is the table where you will DTS the source file.
Please change the table names , procedure name as per what you require.



create table tmpplans(weekending datetime, ssize int, effic int, dailycap int, workdays int, planutil int, demand int,variance int)
create table plans (weekending datetime, ssize int, effic int, dailycap int, workdays int, planutil int, demand int,variance int)

insert into tmpplans values ('5/17/2002', 8, 80, 85, 5, 244,101,142)
insert into tmpplans values ('5/18/2002', 8, 80, 85, 5, 244,120,142)
insert into tmpplans values ('5/19/2002', 8, 80, 85, 5, 244,121,142)

insert into plans values ('5/17/2002', 8, 80, 85, 5, 244,102,142)

Create procedure P1
As
Begin
set nocount on
declare @weekending datetime, @ssize int, @effic int, @dailycap int, @workdays int, @planutil int, @demand int, @variance int, @rowcount int

declare tmpcur cursor for
select * from tmpplans


open tmpcur

fetch tmpcur into @weekending, @ssize, @effic , @dailycap, @workdays, @planutil, @demand, @variance

while @@fetch_status = 0
begin
update plans
set demand = planutil - @demand
where weekending = @weekending
and ssize = @ssize

select @rowcount = @@rowcount

if @rowcount = 0
begin
insert into plans values (@weekending, @ssize, @effic , @dailycap, @workdays, @planutil, @demand, @variance)
end

fetch tmpcur into @weekending, @ssize, @effic , @dailycap, @workdays, @planutil, @demand, @variance

end
close tmpcur
deallocate tmpcur
end

Hope this helps you.

Regards,
Shefali


------------
planz at 5/9/2002 3:39:08 PM


Hi- sorry about that
'weekending' and 'size' would be unique

------------
Shefali Naik at 5/9/2002 1:25:47 PM

Hi Planz,

I have one question, How is the uniqueness of the record decided.
In other words, based on which field(s) do you check if the record already exists.

Regards,
Shefali


------------
planz at 5/9/2002 11:38:09 AM



Hey all.. Sorry I have never written a SP in my life.
so this would be a newbie question for sure.
Using sql server 7.

Here is my table field setup with sample values all fields are numeric
except date

weekending size effic dailycap workdays planutil demand
variance
5/172002 8 80 85 5 244
102 142

A file with source data will be made available in comma delimitted format to
supply a new weekending value, size and demand.

What I will need is first the dts to bring that in. then I am assuming a
stored procedure to be run (this is why I am here)
to add the data from the comma del. file into the table. If the data EXISTS
I would want it to UPDATE the values that are in the dest table but run a
calculation first which would be the planutil minus demand then the result to
be updated intot the record.
if the record from the tab del. file does not exisst in the dest table then
insert it.


another words the logic i have in mind
read the data from the temp tample (where the file gets imported into)
see if the record exists in the live table is it does update it with the calculation of planutil minus demand
if not create the new record in the live table.
I need it to compare..

Someone help me with some code
i thank you kindly in advance

p.s. And good books dedicated to stored procedures??