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.table1

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\)]

"Caroline" <pl***@letsdothatagain.com> wrote in message
news:da**************************@posting.google.c om...

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.table1
gives the following error:
Violation of UNIQUE KEY constraint...
Cannot insert duplicate key...
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
Why?
I didn't ask him to copy the key column; Isn't the SQL Server supposed
to know how to increment the key ?

[Simon Hayes]

"Caroline" <pl***@letsdothatagain.com> wrote in message
news:da**************************@posting.google.c om...

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.table1

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 ?

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