Click here to Skip to main content
15,939,243 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

SQL
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:

SQL
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.
Posted

1 solution

Thumb rule for IDENTITY column INSERT:

By default SQL Server does not allow you to insert a value into an IDENTITY column. You need to explicitly enable IDENTITY INSERT.

Only one TABLE per session can have IDENTITY INSERT set to ON at a time.

You cannot update an IDENTITY column. SQL Server will generate an error if you attempt to do that
 
Share this answer
 
Comments
Ron Anoshi 21-Nov-11 2:43am    
True. I know all that, but that raised 2 questions:
1. I tried (or thought I did...) to enabe it on the table to which I was trying to insert data - Why didn't it work?
2. I tried inserting ALL BUT the identity column, so as to allow it to increment by itself - Why didn't that work?

thx.
Heino Zunzer 21-Nov-11 12:20pm    
with
set identity_insert table1 on
you switched identity insert on, which means, you want to give the identity column explicit values. in this case you must provide the column name Id and values for that column.

What you actually want is

set identity_insert table1 off

then your insert statement above should work just fine.
Ron Anoshi 24-Nov-11 7:36am    
of course you are right, and that makes sense, and I don't know why it didn't work the first time...

TRIGGER IS ALL DONE.
thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900