Insert with no 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)

Insert with no duplicates

Author

Topic

medtech26
Posting Yak Master

169 Posts

Posted - 2007-12-19 : 20:05:10

I'm inserting data from variables in stored procedure and find it difficult to avoid duplications.

Here's the query:
INSERT INTO table (id, term) VALUES (@id,@term)

Any direction would be appreciated.

JasonL
Starting Member

35 Posts

Posted - 2007-12-19 : 20:21:25

Have not done these in a while so not sure about syntax but basically you do
INSERT INTO tableX (id, term)
select @id,@term
from A_single_row_table
where not exists (select 'x' from tableX where id=@id and term=@term)

JasonL
http://blogs.msdn.com/usisvde/
http://ushipoisv.spaces.live.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-19 : 20:35:42

Add a primary or unique constraint to your id column to prevent duplicates.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 01:22:32

IF NOT EXISTS (SELECT * FROM table WHERE id=@id AND term=@term)
INSERT INTO table (id, term) VALUES (@id,@term)

medtech26
Posting Yak Master

169 Posts

Posted - 2007-12-20 : 12:56:13

quote:
Originally posted by JasonL

Have not done these in a while so not sure about syntax but basically you do
INSERT INTO tableX (id, term)
select @id,@term
from A_single_row_table
where not exists (select 'x' from tableX where id=@id and term=@term)

JasonL
http://blogs.msdn.com/usisvde/
http://ushipoisv.spaces.live.com/

Thanks but it does not work (it was my first guess as well)

medtech26
Posting Yak Master

169 Posts

Posted - 2007-12-20 : 12:59:25

quote:
Originally posted by tkizer

Add a primary or unique constraint to your id column to prevent duplicates.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Thanks, can't do that as well. There are multiple id's and terms, that rule applies only if both are duplicate :-)

medtech26
Posting Yak Master

169 Posts

Posted - 2007-12-20 : 13:00:39

quote:
Originally posted by visakh16

IF NOT EXISTS (SELECT * FROM table WHERE id=@id AND term=@term)
INSERT INTO table (id, term) VALUES (@id,@term)

Thanks, I guess this was what I looked for. Going to try it ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources