query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query

Author

Topic

vicki
Posting Yak Master

117 Posts

Posted - 2002-03-28 : 16:13:33

Hi,

Insert into servers1
select *
from servers

and here what error message:
An explicit value for the identity column in table 'servers1' can only be specified when a column list is used and IDENTITY_INSERT is ON.

What is that mean?

Thanks

Jay99

468 Posts

Posted - 2002-03-28 : 16:23:00

It means you have the IDENTITY property set for a column in your Servers1 table. You cannot insert into that column as such . . .

You need to explicitly define the columns in your insert and select.


insert into servers1(ServerName,Location,Nonsense)
select
  ServerName,
  Location,
  Nonsense
from
  Servers

Jay
<O>

Edited by - Jay99 on 03/28/2002 16:23:23

Edited by - Jay99 on 03/28/2002 16:23:45

erybarczyk
Starting Member

3 Posts

Posted - 2002-03-28 : 16:25:56

I believe this means your query is attempting to insert specific values into an IDENTITY column in your table 'servers1'. You need to either change your SELECT statement to not include that column (so the values are not there to be inserted), or else set IDENTITY_INSERT to ON. That is a server setting which means 'let me insert my own values into an IDENTITY column' when it is set to ON. I've never actually done it, so I recommend you research it in ooks Online first

I don't remember the details, but you'll want to set it back to OFF when you're done, if you do it at all.

- Eric

vicki
Posting Yak Master

117 Posts

Posted - 2002-03-28 : 16:41:19

Thanks,

Of course I need to do like your query, however, the table has a lot of columns about 150 columns and I though may be I do the short way but unfortunately it didn't work :)

dsdeming

479 Posts

Posted - 2002-03-29 : 08:04:33

If you need to generate long column lists for such an insert statement try something like this:

select name + ',' from syscolumns where name <> 'your identity column'
and id in( select id from sysobjects where name = 'your table name' )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources