July 24, 2008 at 9:46 am
Hello. I have a situation where new records are added to a [font="Courier New"]Users[/font] table. The fields in the table are [font="Courier New"]UserID, UserName, UserPhone, CompanyName, CompanyID[/font].
When the data comes in, the first four fields are already populated, however I need to run a Stored Procedure to populate the [font="Courier New"]Users.CompanyID[/font]. The logic should be that if the [font="Courier New"]Users.CompanyName[/font] field is present in the [font="Courier New"]Company[/font] table then we should pick up the [font="Courier New"]CompanyID[/font] from there and use it to populate the [font="Courier New"]Users.CompanyID[/font]. If the [font="Courier New"]Users.CompanyName[/font] isn't found in the [font="Courier New"]Company[/font] table then we should insert this company as a new record and then use the newly assigned ID value from the [font="Courier New"]Company[/font] table to populate the [font="Courier New"]Users.CompanyID[/font].
The [font="Courier New"]Company [/font]table has just two fields, [font="Courier New"]CompanyID[/font] and [font="Courier New"]CompanyName[/font].
Hope that makes sense 🙂 Any ideas on what the best way to do this is? Would it require the user of cursors?
July 24, 2008 at 9:50 am
I would definitely NOT require cursors. Could be done as a pretty standard "upsert" (update/insert). Since it's conditional on the insert into the other table, it can either be done in the proc that inserts there, or in a separate "on insert" trigger.
Look up "Update" in Books Online, it will have data on how to do "Update From". That's where you start.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 12:03 am
A Simple Solution Without Cursor
Create Procedure InsertUser
AS
Begin
@UserName varchar(25),
@UserPhone Numeric(18,0),
@CompanyName Varchar(50),
Declare @ID As int
Declare @CId As int
Set @CId=0
Set CId =if EXISTS (select CompanyID From Company Where CompanyName Like @CompanyName +'%')
If @CID<>0
Insert Into Users(UserName, UserPhone, CompanyName, CompanyID) Values(@UserName, @UserPhone, @CompanyName, @CID)
else
begin
Insert into Comapnay (CompanyName) values(@Company)
Set @ID = @@Identity
Insert Into Users(UserName, UserPhone, CompanyName, CompanyID) Values(@username, @userphone, @CompanyName, @ID)
end
end
Note that companyname must be unique.
July 28, 2008 at 4:17 am
Dear thomas ,
The logic described by dolly is right but I think that the syntax is incorrect...
Set CId =if EXISTS (select CompanyID From Company Where CompanyName Like @CompanyName +'%')
I think dolly was trying to write
select @CID = CompanyID From Company Where CompanyName Like @CompanyName +'%')
Also, the above query will always give you the last CompanyID IF the the above query more than one record.
Another assumption in the code is that the "Company" Table HAS the Identity column on companyID. If yes, its fine.
Enjoy T-SQL...
Atif Sheikh
July 28, 2008 at 10:16 am
Thanks for your help everyone. I'll give it a try 😎
Tom
July 29, 2008 at 7:05 pm
Obviously, what you are trying to do can be done and it makes for a good exercise in solving problems using SQL. However, my advice is don't do it at all.
What happens if someone enters "Smith and sons" (which is not listed in the Company table) instead of "Smith and son" (which is)? Bad mojo. You have bypassed the nice data integrity checking that could prevent such simple errors. Believe me, you will pay for this later!
You have a [font="Courier New"]Company[/font] table and a [font="Courier New"]Users[/font] table. The [font="Courier New"]Company[/font] table contains the independent entity and the [font="Courier New"]Users[/font] table contains the dependent entity with, I assume, a foreign key on [font="Courier New"]Users.CompanyID[/font] defining the relationship between the two. This is fine. This enforces the restriction that a company must be created before a user associated with the company. If one of your users (the ones running the application) tries to add a user before adding the company, you want there to be a nice, friendly little error message explaining the situation. "You screwed up! Try again."
Trying to make the data a little too friendly has also caused you to commit a data modeling error. You have [font="Courier New"]CompanyName[/font] and [font="Courier New"]CompanyID[/font] both as part of the User record. But [font="Courier New"]CompanyName[/font] relies on [font="Courier New"]CompanyID[/font] -- meaning you have violated 3nf.
So quit trying to get too fancy, remove [font="Courier New"]CompanyName[/font] from the Users table and let your users know that a company must be defined before anyone connected to the company. Your data will be nothing but the better for it.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
July 30, 2008 at 2:56 am
Hi Tomm,
I hear what you're saying and agree with it. Now I have to fess up to being a tad disingenuous with my original post. My real life example has nothing to do with Customers and Companies, that just always seems to be the simplest example to use to explain what I'm trying to do. But more to the point, I didn't give you the full facts.
I'm receiving MSMQ messages through a private message queue. These are coming from a (remote) master database, via BizTalk, and are converted from raw XML though XSL into holding tables in my secondary (local) database. From these holding tables I then have to pick and choose from the available data to update or insert records into the proper tables in my secondary database.
You're dead right that if an update comes over from the master database where a typo is corrected in a 'company' name then, if we're using the company name as the identifier, we'll end up with a new company, not an update on the typo. In reality we're actually using unique identifying codes so that shouldn't be a problem.
With MSMQ message by their very nature being asynchronous, we will sometimes get the user before the company and sometimes the other way round.
Anyway, apologies, I just wanted to know how to carry out the general action and I was a bit lazy in explaining my actual setup/situation. Sorry! :blush:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply