inserting results into two different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

inserting results into two different tables

Author

Topic

riggers
Starting Member

2 Posts

Posted - 2003-12-02 : 05:56:45

I've got the following procedure:
ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS
select
A_ProspectPipeline.ID,
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())
then '1'
else '13'
end
end)as [CY],

(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())+1
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())+1
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())+1
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())+1
then '1'
else '13'
end
end)as [NY]

from a_ProspectPipeline where A_ProspectPipeline.ID = @ID

What i need to do is insert the two returned values [NY] + [CY] into two different tables.
Can anyone help me with this?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-02 : 10:21:28

One option would be to set those values to an @variable and do your inserts.

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-03 : 12:16:24

Nah...he could get more than 1 row...

Create 2 sql statements?

Use a trigger on the first Table?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-03 : 12:18:32

Plus it's a malformed SQL statement anyway...

SELECT CASE WHEN x=1 then 'a'
when x=2 then 'b'
when x=3 then 'c'
else 'd'
end
...

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-03 : 13:14:48

quote:
Originally posted by X002548

Nah...he could get more than 1 row...

Good point Brett,

If multi row is a possibility then another option might be to create table variables.

mohit_sharan
Starting Member

22 Posts

Posted - 2003-12-04 : 00:16:25

Hay,

Why dont u use a after insert trigger or Instead of After trigger in the first table and use Iserted inbuilt table of SQL Server to insert the selective columns which u want to insert into two different tables. As it works for each inserted records.

-Mohit.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources