November 14, 2009 at 7:16 pm
hi,
Using SSIS,
i am importing data from access to sql,
i am dumping all access values into staging sql table and from there into final sql table
i am getting "Violation of PRIMARY KEY constraint 'aaaaaCompany_PK'.
Cannot insert duplicate key in object 'dbo.company'."
my destination sql table is empty.
the table scripts is:
CREATE TABLE [dbo].[Company](
[CompanyNumber_PK] [int] NOT NULL,
[CompanyName] [varchar](50) NULL,
other 40 rows)
CONSTRAINT [aaaaaCompany_PK] PRIMARY KEY NONCLUSTERED
(
[CompanyNumber_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
and some more other references
--
i think there is something do with : [aaaaaCompany_PK] PRIMARY KEY NONCLUSTERED and [CompanyNumber_PK]
help me out
Thanks alot
November 15, 2009 at 4:17 am
It looks like you have duplicate values for [CompanyNumber_PK] in your table.
You can check for duplicates with the following code:
SELECT CompanyNumber_PK
FROM YourStagingTable
GROUP BY CompanyNumber_PK
HAVING COUNT(*) > 1
Second question: why do you make the company number a nonclustered Index? Since I don't see any clustered index for that table you end up with a hash table... Kinda strange approach. I'd recommend to make your [aaaaaCompany_PK] a clustered one. Oh, and once you're at it: change the name something more descriptive, like CI_Company_CompanyNumber_PK (clustered index on table Company column CompanyNumber_PK).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply