SQL Express: Trying to insert into a table with an auto-incremented PK field.

Question

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 20-Nov-11 4:22am

Ron Anoshi

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Ganesan Senthilvel · Answer 1 · 2011-11-20T17:10:00

Solution 1

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

Posted 20-Nov-11 17:10pm

Ganesan Senthilvel

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.