How to prevent duplicate population in a stored pr

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 2005 Forums

Transact-SQL (2005)

How to prevent duplicate population in a stored pr

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-06-10 : 06:46:31

Hello All,

I created a stored procedure that loads data into a table… What I want to do is to prevent the stored procedure to load data multiple time for the same date, how can I accomplish this task? I think the only field in that table structure that can prevent duplicate data is the date/time field. Below is the column name and value, please advice. Thanks.

RECORD_DATE
2009-06-09 09:14:46.040

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 06:49:30

How do the code for stored procedure look like?

E 12°55'05.63"
N 56°04'39.26"

pras2007
Posting Yak Master

216 Posts

Posted - 2009-06-10 : 08:37:25

Thanks Peso for the response. Below is the entire code:

CREATE Procedure [dbo].[Usp_Run_Batch] (@B_Type varchar(50)OUTPUT)
as

BEGIN
Declare @Select_SQL Varchar(Max)
Declare @M_ID Integer
Declare @M_Query Varchar(Max)
Declare @B_Status Varchar(50)
Declare @Max_Batch_ID Varchar(20)
Declare @BatchId uniqueidentifier

set @BatchId = newid()

insert into edge_etl_batch (BatchID,batchtype)
values (@BatchId,@B_Type)

select @BatchId --display for output

-- Obtain the list of all measures for the batch_type that was passed in.
Declare cur_audit cursor for
select m_id, m_requery, b_status
from SData.dbo.measure_audit
where b_type = @B_Type

open cur_audit
fetch next from cur_audit into @m_id, @m_query, @b_status

While @@Fetch_Status = 0
BEGIN

Set @Select_SQL =
' Select batchid, getdate(), ' + Cast(@m_id as varchar(20)) + ',(' + @M_Query + ')' +
' From sdata.dbo.batch' +
' where a_ind = 0 and b_type = ' + '''' + @B_Type+ '''' +
' and (b_status = ' + '''' + @B_Status + '''' +
' or ' + '''' + @B_Status + '''' + '= ' + '''ALL''' + ')'

Insert into SData.dbo.audit (B_id, a_date, m_id, m_value)
Exec (@Select_SQL)
Print @Select_SQL

fetch next from cur_audit into @m_id, @m_query, @b_status
END

Update SDATA.dbo.edge_etl_batch
set Audit_Ind = 1
WHERE BatchDate IN (Select top 1 BatchDate from SDATA.dbo.edge_etl_batch
order by BatchDate desc)

Close cur_audit
Deallocate cur_audit

END

pras2007
Posting Yak Master

216 Posts

Posted - 2009-06-10 : 08:38:38

I forgot to mention that, RECORD_DATE is Batchdate. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 12:47:21

Use the suggestion I posted here for you.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127348

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-10 : 14:16:09

quote:
Originally posted by pras2007

Hello All,

I created a stored procedure that loads data into a table… What I want to do is to prevent the stored procedure to load data multiple time for the same date, how can I accomplish this task? I think the only field in that table structure that can prevent duplicate data is the date/time field. Below is the column name and value, please advice. Thanks.

RECORD_DATE
2009-06-09 09:14:46.040

how frequently will this sp be executed in a day? are you looking for a method to capture only delta changes each time?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources