Enter only new values - 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

General SQL Server Forums

New to SQL Server Programming

Enter only new values

Author

Topic

user786
Starting Member

1 Post

Posted - 2011-11-08 : 13:43:42

How can i write SQL INSERT IntO Statement that will add in the database only new values in the database, and skip the ones already in the database?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-08 : 13:58:04

Well you should have a primary key constraint on your table that will prevent the duplicates. So from your application, attempt the INSERT and catch the PK violation error if it's a dupe. I wouldn't recommend wasting the time to check if it exists already, just catch the error when it does.

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

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-08 : 16:16:06

my guess is they want to "fill in the blanks" where IDENTITES were deleted

oye

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Just_Jeff
Starting Member

7 Posts

Posted - 2011-11-09 : 19:41:50

Are you talking about only inserting one row at a time... like for data entry screens?

If so then you could check for matching records using the Exists() function.

Here is my example (assume that you have put the value to be checked in a variable, in this case @NewName):

declare @NewName varchar(50)
Set @NewName = 'Amazon'

if not exists(Select * from tblCompany where CompanyName = @NewName)
insert into tblCompany
(CompanyName)
values
(@NewName)

If you are looking for a multi-record solution then you could use a select statement in as the criteria for a Contains function like this:

Insert into tblTest1
(Thing1)
select Thing2
from tblTest2
where Thing2 not in (select Thing1 from tblTest1)

Hope that helps!
-Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources