don't insert duplicates - 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

SQL Server 2005 Forums

Transact-SQL (2005)

don't insert duplicates

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-30 : 01:32:41

I don't want to insert duplicate records into a table and many times this may happen so I am doing the following code below.
it is not working and i think it may be because what i am passing is a date in the format of 28/04/2007 and a time in the format of 08:17:45
I know sql doesn't have just a date or a time field so i tried converting the field but it didn't help.

any advice?



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spinsert] 
	-- Add the parameters for the stored procedure here
	@name varchar(100),
	@date datetime,
	@time datetime,
	
AS
BEGIN
declare @recexists int
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
select @recexists=count(id) from images where date=@date and convert(varchar,[time],108)=@time name=@name
if @recexists=0
	begin
		insert into images(name,date,[time]) 
values(@name,@date,@time)

	end 
    -- Insert statements for procedure here
	
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-30 : 01:59:54

Why are you storing data and time in separate datetime field ?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-30 : 03:00:06

your right - that is a good point... I will try to combine and see what happens -- thanks

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-30 : 03:36:27

i changed it to a date and time together but it's still not working

any ideas?

Kristen
Test

22859 Posts

Posted - 2007-04-30 : 04:47:49

There is a syntax error in:

select @recexists=count(id) from images where date=@date and convert(varchar,[time],108)=@time name=@name

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-30 : 08:46:50

Do not convert your times to VARCHAR for the comparison, leave them as the correct datatype -- datetime.

Separating Dates from times actually can be very handy and make your SQL shorter and more efficient. see: http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

and for a bunch of useful datetime functions that will help you:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

And finally, as for not inserting duplicates -- what is the primary key of your table? that is always the primary way to avoid inserting duplicates. Once we know your table structure and the datatypes you are using and all that, it will be easier to help you fix this stored proc up, it should be pretty easy as this is pretty simple.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources