0

I have a table where I need to insert codes, years, and descriptions. When the table was created, the PKey was Year,Code I'm trying to insert the same code with a different year into the table

So...

Existing values

Year  | Code| Description
2016  |  A  |  Code A
2016  |  B  |  Code B
2016  |  C  |  Code C

Attempt to Insert

2017  |  A  |  Code A
2017  |  B  |  Code B
2017  |  C  |  Code C

From what I've read, it sounds like this should be valid. But, for some reason, I keep getting the error that

My SQL is:

select *
into #temp
from code_lkp

insert into code_lkp (year,code,description)
select 2017,code,description
from #temp

drop table #temp

Since the combination of the two columns is unique, shouldn't the table accept the insert?

UPDATE....

I ran

select a.name, b.name, c.name
from sys.sysobjects a
inner join sys.syscolumns b
on a.id = b.id
inner join sys.key_constraints c
on c.parent_object_id = a.id
where a.type = 'U'
and a.name = 'code_lkp'

According to the results:

SysObjects | SysColumns | key_contraints
code_lkp   | Year       | code_lkp_pk
code_lkp   | Code       | code_lkp_pk

If I'm not mistaken, this should indicate that both Year and Code are part of the same Primary Key.

Xenoranger
  • 421
  • 5
  • 22
  • 1
    Can you show the contents of the `#temp` table? Are you certain you're only trying to insert each combination once? – Siyual Jun 28 '17 at 20:08
  • I can't show the contents of the temp table. But, basically, it follows as in the example. I'm only trying to insert each combination once (1x). So, I want 2017,A in there once. 2018, A in there once... etc. – Xenoranger Jun 28 '17 at 20:09
  • Should also mention, the temp table is created in the select into statement. – Xenoranger Jun 28 '17 at 20:10
  • Run your insert statement with the `Insert` line commented out and see what is being selected. It sounds like you are trying to insert the same record twice. – Siyual Jun 28 '17 at 20:12
  • it's coming back with 2017 || A, 2017|| B, 2017|| C. Those values are not in the target table. – Xenoranger Jun 28 '17 at 20:15
  • Without seeing your data, I'm honestly not sure what to tell you. I've recreated your tables with the data given, and run your exact queries and it worked without any issues. – Siyual Jun 28 '17 at 20:18
  • I'll keep digging. Thanks!! There may be something else going on with this system that I'm not privy to ATM. – Xenoranger Jun 28 '17 at 20:19
  • 1
    You might also want to check to see if there are any triggers on that table. The error could be coming from one of those, instead. – Siyual Jun 28 '17 at 20:20
  • I did check for triggers. Nothing present. URGH – Xenoranger Jun 28 '17 at 20:21
  • Add real `CREATE TABLE` script for `code_lkp` – Ivan Starostin Jun 28 '17 at 20:29
  • I would check the DDL for the primary key to ensure it is looking at both Year and Code. – Andrew O'Brien Jun 28 '17 at 20:30
  • What's the error ? – Dimitar Tsonev Jun 28 '17 at 20:45
  • I updated above. From what I can tell (querying the sys.key_contraints), it looks like both Year and Code are part of the same primary key. – Xenoranger Jun 28 '17 at 20:48
  • The error is always "Violation of primary key constraint code_lkp_pk. Cannot insert duplicate key object 'dbo.code_lkp'. The duplicate key is (2017, A) – Xenoranger Jun 28 '17 at 20:50
  • I'm thinking your select statement is not returning distinct values, meaning that you have multiple rows with 2017, A values – Sparrow Jun 28 '17 at 20:53
  • That appears to be the issue. I ran a general select query. It looks like all the values are in there now. – Xenoranger Jun 28 '17 at 21:01
  • please post `show create table` result. – Ankit Bajpai Jun 28 '17 at 21:49
  • The issue was resolved. It was a problem of not using the Distinct operator in the select query when selecting into the temp table. ..... thanks Sparrow. – Xenoranger Jun 28 '17 at 22:01

0 Answers0