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
 Don't want to insert same values 2 times.

Author  Topic 

gongxia649
So Suave

344 Posts

Posted - 2006-09-06 : 13:59:37
create proc dbo.usp_CatchAddressException @tableName varchar(500) as
-- alter proc dbo.usp_CatchAddressException @tableName varchar(500) as


begin
set nocount on
declare @sql varchar(700)

-- truncate table tempException
set @sql = 'insert hava_dcde.dbo.STREET_EXCEPTION (id_town, id_voter, pre_value, nbr_exception, date_stamp)
select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20'', getdate()
from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a
where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))+1) not in
(select nm_street from HAVA_DCDE.dbo.state_streets ss)'
-- and a.id_town = + @tableName
-- print(@sql)

exec(@sql)
set nocount off
end
---------------------------------------------------


when i run this SP 2 times, it will insert the values 2 times.
what should i do if i don't want to insert the same values 2 times?
I'm confussed, Should i use update? any input will be appreciated.




eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-06 : 14:07:10
when you run it twice, should the 2nd run generate different data or should it actually be the same?

How about using a primary key or unique key on the table you are inserting into.



-ec
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-06 : 14:09:12
should generate the same records. But i don't want it to be inserted into the table.
Because i might accidentally hit run 2 times.


Primary key will throw me an error. I don't want this.
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_STREET_EXCEPTION'. Cannot insert duplicate key in object 'STREET_EXCEPTION'.
The statement has been terminated.





Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-06 : 14:21:20
quote:
Originally posted by gongxia649
Primary key will throw me an error. I don't want this.
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_STREET_EXCEPTION'. Cannot insert duplicate key in object 'STREET_EXCEPTION'.
The statement has been terminated.



that is exactly what you want. Put error handling in your stored procedure and you will be fine.


-ec
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-06 : 14:26:15
can you teach me how to put error handling?

is it something like @@rowcount?



Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-06 : 14:29:53
here are two very good articles on the subject. read them both completely and you should be set.

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html



-ec
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-06 : 14:32:59
can you help me to write the code first, then i will read it.



Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-06 : 14:39:21
quote:
Originally posted by gongxia649

can you help me to write the code first, then i will read it.



so, this sproc is so critical that you have to have it working in the next 30 seconds?

how about you take 5 minutes and read that article. You might learn something.



-ec
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-06 : 14:42:03
i actually needed it yesterday.



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-06 : 14:47:40
Then you'd better start reading it quickly! We aren't here to do your job for you. Ec already provided what you need to be able to figure this out.

Tara Kizer
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-06 : 14:57:09
if you can show me just one example. i will be able to complete my task.



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-06 : 14:59:24
Examples are in the links.

Tara Kizer
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-06 : 15:02:54
quote:
Originally posted by gongxia649

if you can show me just one example. i will be able to complete my task.



gong, rtfa!

damn, the whole thing is there. The 2nd article has all the examples btw, if you just want to skip to the back of the book.




-ec
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-06 : 15:07:34
i can't find anything error handling for primary key in the articles.


i just found.
This option applies to unique indexes only. It is not available for PRIMARY KEY or UNIQUE constraints.

i will also know. once i write the code for error handling. Will i still see the msg?

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_STREET_EXCEPTION'. Cannot insert duplicate key in object 'STREET_EXCEPTION'.
The statement has been terminated.





Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-06 : 15:12:16
ok, you are killing me.

did you see the part where it talks about @@ERROR? (you can look up @@ERROR in BOL for more information) Or the part about decalaring a variable called @ERR to store the value of @ERROR? what about the section on error handling with dynamic SQL. that might apply to you also since you are executing dynamic sql. Did you see the sample code in the 2nd article showing you this stuff in action? You can literally cut and paste the error handling code out of the 2nd article and plop it down into your sproc and be done.

and yes, your error message goes away.



-ec
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-06 : 23:20:47
Or change your WHERE clause to exclude rows that are already there: WHERE ... and a.id_town not in(select id_town from street_exception).

Your whole design is pervy though - dynamic SQL on the table name. Yuk. Don't read the normalisation guidelines while you're not reading the other articles! :)
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-07 : 09:50:02
Gong,

did you get this to work? I'm curious if you bit the bullet and read through those articles.



-ec
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-07 : 09:53:24
Hmmm, you don't have or take time to read articles about using SQL Server properly, and you have problems with the code you're writing.

Coincidence????

...or...

CONSPIRACY???

I love how you couldn't take time to read, but had plenty of time to write a response(s) saying you didn't have time to read it.

Do you have other people chew your food for you too? Or do you have someone else hire them?
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-07 : 13:20:25
eyechart, the error msg doesn't go away.



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-07 : 13:24:26
quote:
Originally posted by gongxia649

eyechart, the error msg doesn't go away.





Please post your new code with the error handling section.

Tara Kizer
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-07 : 13:42:56
quote:
Originally posted by robvolk

Hmmm, you don't have or take time to read articles about using SQL Server properly, and you have problems with the code you're writing.

Coincidence????

...or...

CONSPIRACY???

I love how you couldn't take time to read, but had plenty of time to write a response(s) saying you didn't have time to read it.

Do you have other people chew your food for you too? Or do you have someone else hire them?



Custom title:
Needed it Yesterday





CODO ERGO SUM
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -