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

Question

3.67/5 (2 votes)

See more:

select facility_id,Facility_Name into #facility2 from tbl_facility where hub=1

that gives me some records.. like

facility_id  facility_name

 1             office1
 2             office2
 3             office3

and so on ,,,

all these facilityid has hub=1

-----------------------------------------
but i need to add one more office in #facility2 whose hub is not equal to 1
--------------------------------------

so i was tryig like this

1)

SQL

select facility_id,Facility_Name into #facility2 from tbl_facility where hub=1 
                        union all 4,'office4'

--it gave me error -->Incorrect syntax near 4.

2)then i tried like this

SQL

select facility_id,Facility_Name into #facility2 from tbl_facility where hub=1 
insert into #facility2 select facility_id,Facility_Name from tbl_facility where facility_id=236

--here office4 has facility_id=236 whose hub is not equal to 1,,,i tried like this.
--giving me error...

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

3)
then i tried like this

SQL

select facility_id,Facility_Name into #facility2 from tbl_facility where hub=1 
 union all
select facility_id,Facility_Name into #facility2 from tbl_facility where facility_id=236

i got error

There is already an object named '#facility2' in the database.

how to solve this ?

4) then i tried like this ...

SQL

select facility_id,Facility_Name into #facility2 from tbl_facility where hub=1 
 insert into #facility2 values(4,'office4')

i got error

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

5) on my 5th attempt...i got the solution just now....

SQL

   create table #facility2
      (
      facility_id int,
      Facility_Name varchar(77)
      )
      
insert into #facility2
select facility_id,Facility_Name from tbl_facility where hub=1 
union all
select facility_id,facility_name from tbl_facility where facility_id=236

But doubt still persist,,,, why the above 4 attempts failed,,,,

Help me regarding this..

Posted 27-Jun-13 6:10am

anurag19289

Updated 27-Jun-13 8:26am

CHill60

v6

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)

CHill60 · Accepted Answer · 2013-06-27T08:35:00

1) union all 4,'office4' is, as the error says, the incorrect syntax. You need to

SELECT 4,'office4'

2) I presume that you set up facility_id with the IDENTITY(1,1) clause - this will be generated automatically by the DBMS. It is still possible to define these on an insert - look up IDENTITY INSERT ON in MSDN

3) Either you already have #facility2 on your database from your previous attempts - in which case you should DROP the table first - or the first part of your query is creating the table and the 2nd part is generating the error. Try

SQL

select facility_id,Facility_Name into #facility2 from tbl_facility where hub=1
insert into #facility2 select facility_id,Facility_Name from tbl_facility where facility_id=236

(There is no need for the union as you're inserting the records into a table)

4) See (2) above.

5) This is the correct way to go about this - by explicitly declaring facility_id int, (i.e. without the IDENTITY clause) you can insert the data directly