April 25, 2011 at 12:35 am
Hi guys:
I have 3 tables. Industry, IndustryAss(Associative table) and Company.
I am modelling a many to many relationship.
The industry table has the unique industry names. The Company table has the unique companies. IndustryAss connects the two with double primary keys coming from both the industry table and company table. The problem that I am having is that I don't know how to write a insert command to update the associative table to connect the two tables. I don't want to insert the combinations manually like 1,1 1,2 1,3 an so on. That would be too hard.
Example Source for the Insert:
CompanyId-----------Company----------Industry-----------IndustryID
-----1-----------------ABC---------------Accounting-------------1
-----1-----------------ABC---------------Finance----------------2
-----1-----------------ABC---------------Lawyers----------------3
-----2-----------------ZZZ------------------IT-------------------4
-----3-----------------CHK---------------Banking-----------------5
These are the table creation scripts.
Industry
CREATE TABLE [dbo].[Industry](
[IndustryId] [int] IDENTITY(0,1) NOT NULL,
[IndustryName] [nchar](100) NULL,
CONSTRAINT [PK_Industry] PRIMARY KEY CLUSTERED
(
[IndustryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IndustryASS
CREATE TABLE [dbo].[IndustryAss](
[IndustryId] [int] NOT NULL,
[Companyid] [int] NOT NULL,
CONSTRAINT [PK_IndustryAss] PRIMARY KEY CLUSTERED
(
[IndustryId] ASC,
[Companyid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IndustryAss] WITH CHECK ADD CONSTRAINT [FK_IndustryAss_Company] FOREIGN KEY([Companyid])
REFERENCES [dbo].[Company] ([CompanyId])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[IndustryAss] CHECK CONSTRAINT [FK_IndustryAss_Company]
GO
ALTER TABLE [dbo].[IndustryAss] WITH CHECK ADD CONSTRAINT [FK_IndustryAss_Industry] FOREIGN KEY([IndustryId])
REFERENCES [dbo].[Industry] ([IndustryId])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[IndustryAss] CHECK CONSTRAINT [FK_IndustryAss_Industry]
GO
Company
CREATE TABLE [dbo].[Company](
[CompanyId] [int] IDENTITY(0,1) NOT NULL,
[CompanyName] [nvarchar](150) NULL,
[Website] [nvarchar](200) NULL,
[IndustryId] [int] NULL,
[ApplicabilityId] [int] NULL,
[OverridingAgent] [nchar](60) NULL,
[Priority] [nchar](60) NULL,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[CompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks in advance,
Martin
April 25, 2011 at 9:08 am
Does the data exist somewhere else or can it be imported? If so you could build an insert based on this data source.
INSERT INTO IndustryAss
SELECT
CompanyId,
IndustryID
FROM Some_Other_Table_...
The SELECT statement by itself should return data like:
1,1
1,2
1,3
If the data doesn't exist in your database and can't be imported I fail to see any other options besides manual data entry. How do you know that Company ABC is in the Accounting, Finance and Lawyers industry?
April 25, 2011 at 10:20 am
Do you know what industry your companies are in? Are you going to be bulk inserting this data, or is the data coming one at a time from an application?
April 25, 2011 at 3:35 pm
Thanks for your responses. But I need to say that there is no companyid and industryid in the source for inserting. This (Ids) will come from the tables mentioned where I inserted unique companies and unique industries. All I need to do is to check that the company is the same in the source and in the table company and that the industry is the same in the source and in the table industry and where it matches insert the unique combination of companyid and industryid.
Thanks in advance.
Martin
April 25, 2011 at 4:24 pm
Hi Guys I have been writing the query to populate the associative entity:
/* Inserts companyid and industryid into industryass table */
INSERT INTO IndustryAss
SELECT DISTINCT dbo.company.CompanyId, dbo.industry.IndustryId
FROM Company, Industry, dbo.source
WHERE dbo.source.company=dbo.Company.CompanyName and dbo.source.Industry=dbo.industry.IndustryName
GO
But I am getting the error:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_IndustryAss_Industry". The conflict occurred in database "VisitationDb", table "dbo.Industry", column 'IndustryId'.
Any Ideas?
Thanks,
Martin
April 26, 2011 at 7:40 am
The foreign key constraint error message would indicate that you are attempting to insert a industry into IndustryAss which doesn't exist in table Industry.
You will need to populate the industry table with every industry that will be inserted.
April 26, 2011 at 9:05 am
SELECT c.CompanyId, i.IndustryId
FROM dbo.[source] s
INNER JOIN dbo.Industry i ON s.Industry = i.IndustryName
INNER JOIN dbo.Company c ON s.company = c.CompanyName
WHERE c.CompanyId IS NOT NULL AND i.IndustryId IS NOT NULL
GROUP BY c.CompanyId, i.IndustryId
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 26, 2011 at 3:00 pm
Thanks guys it worked.
April 26, 2011 at 3:39 pm
martintalero (4/26/2011)
Thanks guys it worked.
Martin, please can you post the code which works? This gives useful feedback to those who have responded to your request for assistance, and may also help others who experience the same problem in future.
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 26, 2011 at 4:07 pm
The following code worked for me.
/* INSERTs companyid and industryid INTO INDUSTRYASS table */
INSERT INTO IndustryAss(CompanyId, IndustryId)
SELECT DISTINCT dbo.company.CompanyId, dbo.industry.IndustryId
FROM Company, Industry, dbo.source
WHERE dbo.source.company=dbo.Company.CompanyName and dbo.source.Industry=dbo.industry.IndustryName
GO
April 27, 2011 at 1:51 am
martintalero (4/26/2011)
The following code worked for me./* INSERTs companyid and industryid INTO INDUSTRYASS table */
INSERT INTO IndustryAss (CompanyId, IndustryId)
SELECT DISTINCT dbo.company.CompanyId, dbo.industry.IndustryId
FROM Company, Industry, dbo.source
WHERE dbo.source.company=dbo.Company.CompanyName and dbo.source.Industry=dbo.industry.IndustryName
GO
Here's the old query:
/* INSERTs companyid and industryid INTO INDUSTRYASS table */
INSERT INTO IndustryAss
SELECT DISTINCT dbo.company.CompanyId, dbo.industry.IndustryId
FROM Company, Industry, dbo.source
WHERE dbo.source.company=dbo.Company.CompanyName and dbo.source.Industry=dbo.industry.IndustryName
GO
Without a column list for the INSERT, the old query was attempting to populate companyid with industryid and vice versa.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply