INSERT INTO... SELECT... Cannot insert duplicate key...

[Caroline]

I want to add the content of a table into another
I tried to copy all fields, except the primary key:

INSERT INTO table2
(field2, field3, field4, ...)
SELECT field2, field3, field4, ...
FROM anotherDB.dbo.t able1

gives the following error:
Violation of UNIQUE KEY constraint...
Cannot insert duplicate key...

Why?
I didn't ask him to copy the key column; Isn't the SQL Server supposed
to know how to increment the key ?

[Tobes \(Breath\)]

Re: INSERT INTO... SELECT... Cannot insert duplicate key...

"Caroline" <plize@letsdoth atagain.com> wrote in message
news:da07e661.0 311240800.4bc85 e5c@posting.goo gle.com...[color=blue]
> I want to add the content of a table into another
> I tried to copy all fields, except the primary key:
>
> INSERT INTO table2
> (field2, field3, field4, ...)
> SELECT field2, field3, field4, ...
> FROM anotherDB.dbo.t able1
> gives the following error:
> Violation of UNIQUE KEY constraint...
> Cannot insert duplicate key...[/color]

The SQL Server will know how to increment the key if the field is an
IDENTITY field. Perhaps the field requires you to set your own unique key?

Tobes
[color=blue]
> Why?
> I didn't ask him to copy the key column; Isn't the SQL Server supposed
> to know how to increment the key ?[/color]

[Simon Hayes]

Re: INSERT INTO... SELECT... Cannot insert duplicate key...


"Caroline" <plize@letsdoth atagain.com> wrote in message
news:da07e661.0 311240800.4bc85 e5c@posting.goo gle.com...[color=blue]
> I want to add the content of a table into another
> I tried to copy all fields, except the primary key:
>
> INSERT INTO table2
> (field2, field3, field4, ...)
> SELECT field2, field3, field4, ...
> FROM anotherDB.dbo.t able1
>
> gives the following error:
> Violation of UNIQUE KEY constraint...
> Cannot insert duplicate key...
>
> Why?
> I didn't ask him to copy the key column; Isn't the SQL Server supposed
> to know how to increment the key ?[/color]

There could be several reasons - a UNIQUE constraint on the target table as
well as the primary key; a trigger on the target table; a cascading foreign
key violating a constraint on another table etc.

To get a good answer, you will need to post the full DDL (the CREATE TABLE
statements) for both tables, including all keys and constraints, as well as
your exact INSERT statement.

Simon