September 16, 2006 at 12:15 am
Hello,
I posted recently regarding how to set up a database I am creating. I have followed the advice of the posters (thanks for the help), but now have a follow up question. Surprise, surprise.
You can see the first post here: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=307058
So the quick break down. I have a general Customers table, which holds a universal CustomerNo, CustomerType, and the CustomerId of the specific type of customer. There will be a table for each type (residential, commercial, and educational) which will be mapped to this universal Customers table.
What would be the best way to go about inserting a new customer. I know there must be an efficient way to do this, or at least more so than the roundabout ways I know of.
Let me give an example.
Say I want to insert a new Residential Customer. This means that there will be an insert into the residential_customers table. There also must be an insert in the front-end Customers table, which will include the CustomerId mapping to the residential_customers table. Here is what it would look like.
Customers
--------------
CustomerNo: 23 (identity)
CustomerTypeId: 1 (this is residential)
CustomerId: 732
Residential_Customers
------------------
CustomerId: 732 (identity)
FirstName:
etc.
So any ideas?
September 16, 2006 at 8:47 am
Sorry but there's no way to code an insert in 2 tables at once.
You have to do something like this :
Insert into dbo.Customers
--fetch the new ID
SET @MyNewID = SCOPE_IDENTITY()
Insert into dbo.Mapping (...) values (@MyNewID)
September 16, 2006 at 12:45 pm
U can use trigger:
CREATE TRIGGER tri_ins_Residential_Customers
on Residential_Customers
FOR INSERT
as
--Residential_Customers
Declare @CustomerId int,
@FirstName varchar(50)
@CustomerNo int
--SET @CustomerNo = SCOPE_IDENTITY()
select @CustomerId = CustomerId,
@FirstName = FirstName
from inserted
If Not exists (SElect 1 from Customers where CustomerId =@CustomerId and CustomerTypeId=1)
Begin
Insert into dbo.Customers(CustomerTypeId,CustomerId)
select 1,@CustomerId
End
Go
September 16, 2006 at 1:53 pm
How does that insert in 2 tables at once?
And how does it work when you insert more than one row at the time?
September 17, 2006 at 10:09 pm
RBAR!
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2006 at 3:28 am
@adam-2: You should use a Stored Procedure in which you use transactions. With a transaction you can safely insert all the data you want, also spread over several tables. See Sreejith Sreedharans post what T-SQL commands you should use.
The only disadvantage of this solution is that you might need a lot of parameters for shooting all the data from your code into the stored procedure. But trust me, it's the best way!
@Sreejith Sreedharan: I shouldn't use a trigger in this case because you don't know what data needs to be inserted in the other tables.
September 18, 2006 at 5:33 am
I agree with the previous post, a stored procedure is probably the best solution. But the trigger can be written such as:
CREATE TRIGGER tri_ins_Residential_Customers
on Residential_Customers
FOR INSERT
as
insert into Customers(CustomerTypeId, CustomerId)
select 1, CustomerId
from inserted i
where not exists (select 1 from Customers c where c.CustomerTypeId = 1 and c.CustomerId = i.CustomerId)
Go
No need for variables to hold the values.
September 18, 2006 at 8:01 am
I used to do something like this :
SELECT dbo.fnGetCustResidId(), CustomerId
from inserted i
...
I made a udf that returned the type (ID) of something.
That way I could always call the UDF in any SP, view, function or trigger and change the function if (god forbid) the ID had to be changed.
That saved my life only once but it was still worth it.
September 18, 2006 at 11:46 am
Since I am setting up all my queries using Stored Procedures, it was easy to implement it using the method above (SCOPE_IDENTITY).
Thanks for all your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply