SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Don't want to insert same values 2 times.
 New Topic  Topic Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 8

gongxia649
So Suave

Azores
344 Posts

Posted - 09/06/2006 :  13:59:37  Show Profile  Visit gongxia649's Homepage
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/06/2006 :  14:07:10  Show Profile
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

Azores
344 Posts

Posted - 09/06/2006 :  14:09:12  Show Profile  Visit gongxia649's Homepage
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.






Edited by - gongxia649 on 09/06/2006 14:13:27
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/06/2006 :  14:21:20  Show Profile
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

Azores
344 Posts

Posted - 09/06/2006 :  14:26:15  Show Profile  Visit gongxia649's Homepage
can you teach me how to put error handling?

is it something like @@rowcount?



Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/06/2006 :  14:29:53  Show Profile
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

Azores
344 Posts

Posted - 09/06/2006 :  14:32:59  Show Profile  Visit gongxia649's Homepage
can you help me to write the code first, then i will read it.



Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/06/2006 :  14:39:21  Show Profile
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

Azores
344 Posts

Posted - 09/06/2006 :  14:42:03  Show Profile  Visit gongxia649's Homepage
i actually needed it yesterday.



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 09/06/2006 :  14:47:40  Show Profile  Visit tkizer's Homepage
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

Azores
344 Posts

Posted - 09/06/2006 :  14:57:09  Show Profile  Visit gongxia649's Homepage
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

USA
37134 Posts

Posted - 09/06/2006 :  14:59:24  Show Profile  Visit tkizer's Homepage
Examples are in the links.

Tara Kizer
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/06/2006 :  15:02:54  Show Profile
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

Azores
344 Posts

Posted - 09/06/2006 :  15:07:34  Show Profile  Visit gongxia649's Homepage
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.






Edited by - gongxia649 on 09/06/2006 15:08:44
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/06/2006 :  15:12:16  Show Profile
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

Edited by - eyechart on 09/06/2006 15:14:13
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 09/06/2006 :  23:20:47  Show Profile
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/07/2006 :  09:50:02  Show Profile
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

USA
15676 Posts

Posted - 09/07/2006 :  09:53:24  Show Profile  Visit robvolk's Homepage
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

Azores
344 Posts

Posted - 09/07/2006 :  13:20:25  Show Profile  Visit gongxia649's Homepage
eyechart, the error msg doesn't go away.



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 09/07/2006 :  13:24:26  Show Profile  Visit tkizer's Homepage
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)

USA
7020 Posts

Posted - 09/07/2006 :  13:42:56  Show Profile
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
Page: of 8 Previous Topic Topic Next Topic  
Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000