Hi,
I am trying to select a row from one table and insert it into another, when the 1st column in the tables is a primary key, auto-incremented.
(They are actually the same table on 2 different DBs, which I'm trying to create a trigger for...)
I naturally tried to select all colums but the auto-incremented one, and insert those, so that the auto incremented column will be filled automaticly,
(They do not have to be with the same value on the two DBs, but I figgured if the triggers works right they probably will be...)
by doing:
INSERT INTO DB1.Table1
(Name, Phone, FAX, Address)
SELECT
max.Name, max.Phone, max.FAX, max.Address
FROM DB2.Table1 as max
WHERE max.Id = (SELECT MAX(Id) FROM DB2.Table1)
Meaning that I selected and inserted all the columns BUT the 1st one (Id) which is the problematic one...
And the condition in the end is done to make sure that I get the last one added...
This does not work, so I also tried adding:
SET IDENTITY_INSERT Table1 ON
GO
that I read about somewhere...
In both cases I get:
Quote:
Msg 545, Level 16, State 1, Line 2
Explicit value must be specified for identity column in table 'Table1' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
Any ideas?
Ron.