Identity_insert ?/???

When I try using the "insert into select from"....for some of my tables I get this error:

An explicit value for the identity column in table 'db1' can only be specified when a column list is used and IDENTITY_INSERT is ON

i got some help frm google.
SET IDENTITY_INSERT YourTable ON

INSERT INTO YourTable
SELECT * FROM YourOtherTable

SET IDENTITY_INSERT YourTable OFF


i used it bt again error comes as syntax error for both lines ON/OFF
wht does it mean by" IDENTITY_INSERT is ON"


wht to do?
thanks in advance

Quote:

---

Originally Posted by ash007

When I try using the "insert into select from"....for some of my tables I get this error:

An explicit value for the identity column in table 'db1' can only be specified when a column list is used and IDENTITY_INSERT is ON

i got some help frm google.
SET IDENTITY_INSERT YourTable ON

INSERT INTO YourTable
SELECT * FROM YourOtherTable

SET IDENTITY_INSERT YourTable OFF


i used it bt again error comes as syntax error for both lines ON/OFF
wht does it mean by" IDENTITY_INSERT is ON"


wht to do?
thanks in advance

---

The identity column, at least in SQL 2000, is maintained by the database itself. It's "touchy" about people messing with it. A primary use is to for the table to have a unique identifier versus maintaining unique keys programmatically. Normally you don't want to be changing it except for controlled DB maintenance.

Here is a link for maintaining it outside the scope of the DB.

http://www.sqlteam.com/article/under...entity-columns

Without knowing what you are doing I'm thinking your approach is wrong if someone else set up the table and you are just using it. Identity columns are not meant to be overwritten in most cases. It is basically a primary key.

Moved to Database Development

Unless you have a specific reason to specify the identity value yourself...

Then simply leave that column out of the INSERT list.

It might seem odd - but by not specifying that column in the list - it will get a value automatically.

What are you actually trying to achieve??

Does the table that you're inserting from have the primary key you need in it already? If so, take the IDENTITY property off of the table that you're inserting into. That's there simply to create a unique primary key for each record in cases where you don't have anything unique to identify each row yourself. If not, don't include the identity column in what's being copied.

Also, doing a SELECT * in an insert isn't the best programming practice, because if anything changes about either table, your whole application will then bomb. It can be time-consuming to write out each column name, but it makes for far less mantenance headaches in the future.

Thanks 2 all
the problem was same ^^