December 17, 2007 at 11:13 pm
hello my friends
i have a SQL Database with 2 tables : Company and Product
Fields in Company :
- CompanyID ( PK )
- Name
- RegDate
Fields in Product :
- CompanyID ( FK )
- ProductName
its clearly definite that one company can have more than one product so there's a one to many relation.
how can i fill both table ? i mean i fill Company and company's products in one time !!?
i hear that i should define a view with joining this two tables and define a trigger on them to fill Company table
and then select CompanyID and Insert products on product table .
i'll appreciate if you help me on this !
December 18, 2007 at 2:04 am
What is the natural key for each company? IOW: how can you tell companies apart other than using the surrogate CompanyID key?
When you answer the question above you'll also know how you can tell which product(s) belong to individual companies.
Then insert all the companies into the Companies table, and use the natural key to build a correlated set of products that you insert into the Products table.
For a better answer, please, post proper DDL, sample data and expected results.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
December 18, 2007 at 2:37 am
Matija Lah (12/18/2007)
What is the natural key for each company? IOW: how can you tell companies apart other than using the surrogate CompanyID key?When you answer the question above you'll also know how you can tell which product(s) belong to individual companies.
Then insert all the companies into the Companies table, and use the natural key to build a correlated set of products that you insert into the Products table.
For a better answer, please, post proper DDL, sample data and expected results.
i want to INSERT both tables ( Company , Product ) at a same time !
think about this :
declare @Id int
INSERT INTO A (CompanyName)VALUES('Iran');
SELECT @Id=@@IDENTITY
INSERT INTO B (CompanyID,Tel)VALUES(@Id,'0912')
this can be a procedure with 2 parameters ( CompanyName,Telephone)
its clear that i can do with this but i want know about triiger and view in this senario that i told in first pozt!
December 18, 2007 at 3:05 am
I understand what you want to achieve, but you haven't answered any of my questions. It is of vital importance that you identify the relationship between the two sets. Sample data would be helpful.
Frankly, I see no need for using views and triggers to achieve your goal.
If your data is available and you can properly correlate the two sets (i.e. products vs. companies) then this is all you need to design the insert queries. What is your source (e.g. a CSV, an external data source)?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
December 18, 2007 at 1:29 pm
Why do you need to insert into both tables in one query? All you end up with is a set of queries hidden in a trigger on this view. Isn't it simpler to write a stored proc that'll do the job?
Trigger will execute once per statement - so actually you may have more than one row being inserted. If you don't have natural key, how will you know that given product goes to given company?
...and your only reply is slàinte mhath
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply