need help with a query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

need help with a query

Author

Topic

ushavellala
Starting Member

10 Posts

Posted - 2013-11-08 : 01:00:23

Hello,

I have this stored procedure and I need to run this for everyday. I have written a query for the same purpose. But I don't want the same records to be inserted when I run the stored proc again on the same day

Here is my query

CREATE PROCEDURE [dbo].[sp_Amt]
AS

declare @Date datetime
select @Date = getdate()

if(@Date = getdate())

BEGIN
INSERT INTO FactTable(PID, Amt, RecordCreatedDate)
select D. PID, SUM(F.Amt),Convert(varchar(11), getdate(),101)
from FactGenTable F
left join DimTable D
on D.CID = F.CID and D.AccIndex = F.AccIndex
where D.AccSeg5 > 1 AND F.Calyear = 2013 AND F.Acc Like '%1200-002%'
group by D.PID
END

Can you please help me with this ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-08 : 01:21:24

just add a condition using WHERE NOT EXISTS


CREATE PROCEDURE [dbo].[sp_Amt]
AS


declare @Date datetime
select @Date = getdate()

if(@Date = getdate())

BEGIN 
INSERT INTO FactTable(PID, Amt, RecordCreatedDate) 
select D. PID, SUM(F.Amt),Convert(varchar(11), getdate(),101) 
from FactGenTable F
left join DimTable D
on D.CID = F.CID and D.AccIndex = F.AccIndex
where D.AccSeg5 > 1 AND F.Calyear = 2013 AND F.Acc Like '%1200-002%' 
AND NOT EXISTS (SELECT 1 FROM FactTable WHERE PID = D.PID)
group by D.PID
END

I'm assuming PID is key column in Fact. otherwise use the correct key columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ushavellala
Starting Member

10 Posts

Posted - 2013-11-08 : 01:43:29

Thank you very much for your prompt response!! That totally worked. One question, how do you get to know when I post a question, do you get an email alert ? Because I don't get anything as such and was wondering if there is one

Thanks,
US

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-08 : 01:46:02

quote:
Originally posted by ushavellala

Thank you very much for your prompt response!! That totally worked. One question, how do you get to know when I post a question, do you get an email alert ? Because I don't get anything as such and was wondering if there is one

Thanks,
US

Nope ...Nothing like that
Its just that I was online and saw the post

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ushavellala
Starting Member

10 Posts

Posted - 2013-11-08 : 01:51:50

Oh, Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources