Stopping Duplicate records from entering SQL Serve

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)

Stopping Duplicate records from entering SQL Serve

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2008-04-22 : 10:40:08

Hello All,

I have a DTS package that loads data from MS Access to SQL Server, but the problem that I’m having is if that process is ran twice it will load duplicate records into SQL Server. Does anyone know how to stop this from happening? I wrote the below script, but it is given me an error. The error is listed below:

Error Source: Microsoft JET Database Engine
Error Description: Invalid SQL statement; expected ‘DELETE’,’INSERT’,’PROCEDURE’,’SELECT’, or ‘UPDATE’.

SCRIPT:

--Initialize variables
DECLARE @RptDateDetail AS datetime
SET @RptDateDetail = dateadd(day,-1,getdate())

--remove potential duplicate data
DELETE FROM TABLE_A
WHERE RPT_DATE_DETAIL = @RptDateDetail

Select
`COL_1`,
`COL_2`,
`COL_3`,
`COL_4`,
`COL_5`,
`COL_6`
From
`TABLE_A`

When I run the select statement by itself it works, but creates duplication if ran more than once. Please help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:43:26

This line

SET @RptDateDetail = dateadd(day,-1,getdate())

will include hour, minute, second and milliseconds.

try this instead

SET @RptDateDetail = DATEDIFF(DAY, 1, GETDATE())

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources