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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-24 : 09:52:46
|
| Hugh writes "I'm building a SQL Server 2000 database which has a table for Letter subscribers containing FirstName, LastName, StreetAddress, AddressLine2, email fields. Cities, PostCodes and Countries are in separate tables, according to normalization principles. I have foreign keys in the subscribers table related to a primary key (autonumber) in each of the separate tables.I'm trying to write a stored procedure that will take input and distribute it among the separate tables, using @@IDENTITY. But if a City for a new subscriber already exists in the Cities table, I don't want a duplicate entry - but I do want the existing City ID entered as a foreign key in the Subscribers table. Similarly for PostCodes and Countries.Help, please." |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-24 : 10:33:31
|
| the first Alarm your Question Raises is Your Primary Key is AutoNumber. you got to read this Article http://www.sqlteam.com/item.asp?ItemID=2599its quite easy doing what you want.Just check if a record exists in the table with the key you want to add . if it doesnt add that record . otherwisesomething like this should help you.IF EXISTS (select * from country where countrycode=@mcountrycode) begin insert into parenttable(,,,...) values(,,,...) endelsebegin insert into country(countrycode,....) values(@mcountrycode,....).....endHTH-------------------------Graz's Baby is my Master:)Edited by - Nazim on 12/24/2001 10:46:51 |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-24 : 12:16:44
|
quote:
the first Alarm your Question Raises is Your Primary Key is AutoNumber.
The topic of IDENTITY fields as Primary Key is an often-debated topic in the industry. And I can strongly argue both sides of it. But besides all that, Nazim's notes on using IF EXISTS should be helpful. Also remember that the value in @@IDENTITY is based on the last command, so be sure to stuff it into a variable of your own so that you can use it in other parts of your sproc. Something like this...Declare @NewID intINSERT INTO tbl1 ....SET @NewID = @@IDENTITY |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-24 : 23:44:18
|
| Merry Christmas all of U!Ajarn , but in Hugh case there are well defined key's countrycode,citycodes so i strongly feel that using AutoNumber is avoidable.i would like to hear on what kind of cases you will like to go for Autonumber as primary key.-------------------------Graz's Baby is my Master:) |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-26 : 12:30:20
|
Well for Hugh's scenario, autonumbers may not be needed or useful for the secondary tables. Personally, I have not broken out city, postal codes, or country to separate tables as he describes.I tend to use IDENTITY fields a LOT. This has come about out of a great distaste for the headaches I gotten from developing schemes to cascade updates of primary key fields. Perhaps this distaste is out of proportion to reality, but it's there nonetheless. I have no moral compunction to use meaningful keys. If I want, for example, my customer service reps to be able to look up somebody based on SSN, then I can build that functionality without SSN being the primary key. Apply an index to the field, and you get pretty good response  .Now I don't want to rekindle the GREAT IDENTITY KEY DEBATE, but since you asked for an example, here is, in my opinion, the easiest example to argue: Customer table. The number one most often recommended "meaningful key" for a Customer table in the U.S.A. is the Social Security Number (SSN). I don't think any other field or combination of fields is worth considering, unless you are a phone company. And even then, it's debatable. Well the problems with SSN are these: 1) Not everyone has one, so you have to put in a dummy value for those who don't. 2) Data Entry errors can cause this field to have to be updated. 3) There are legal issues, although I've never heard of this actually being enforced. 4) There is a privacy backlash going on right now with a growing resentment toward having to give out SSN's where it seems totally irrelevant. 5) If you go international, this number doesn't apply, although there may be other ID's that are similar.Given all of the above, it just seems to me to be a lot less hassle to give the customer a "Customer ID" or "Account Number" that is an IDENTITY field. |
|
|
|
|
|
|
|
|
Inserting Into Related Tables