I have a stored procedure that inserts data from one table to another when I press the command button in Access 2003. Now the procedure is that when physical files go to storage the records go to the storage database called Corovan. So it takes database from the Termination Database to the corovan Database. The problem is it does it by dates so if some 2005 are already in the corovan database and I try to enter in more through the stored procedure I get a primary key error message. What I need to add to the stored procedure is a command that will only add the info that isnt already in the database 2005 or not without breaking any primary key rules, does that make sense???




ALTER PROCEDURE Corvan_Insertsp
   @pdBegin DATETIME = NULL
, @pdEnd DATETIME= NULL
AS

IF @pdBegin NOT BETWEEN '2003-01-01' AND '2003-12-31' -- "bad" begin date
   SET @pdBegin = DateAdd(year, -2, GetDate()) -- Back up two years

IF @pdEnd IS NULL -- If no end date
   SET @pdEnd = DateAdd(year, 1, @pdBegin) -- One year after the begin date

INSERT INTO [GamingCommissiondb].[dbo].[Corovan_Table](
[TM #], [FirstName], [LastName]
,[SS #], [TerminationDate], [Voluntary or Involuntary]
)SELECT
[TM #],[FirstName],[LastName]
,[SocialSecurityNumber], [TerminationDate], [VoluntaryorInvoluntary]
FROM TERMINATION
WHERE TerminationDate BETWEEN @pdBegin AND @pdEnd
RETURN

INSERT INTO [GamingCommissiondb].[dbo].[Corovan_Table](
[TM #], [FirstName], [LastName]
,[SS #], [TerminationDate], [Voluntary or Involuntary]
)SELECT
t.[TM #],t.[FirstName],t.[LastName]
,t.[SocialSecurityNumber], t.[TerminationDate], t.[VoluntaryorInvoluntary]
FROM TERMINATION t
left join [GamingCommissiondb].[dbo].[Corovan_Table] c on t.[TM #] = c.[TM #]
WHERE t.TerminationDate BETWEEN @pdBegin AND @pdEnd
and c.[TM #] is null

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

I tried that and this is the message I get


Line 9: Incorrect syntax near '@pdBegin'.