只有在使用列列表且IDENTITY_INSERT为ON时，才能指定表'＃facility2'中标识列的显式值。 [英] 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.

问题描述

从tbl_facility选择facility_id，Facility_Name到＃facility2，其中hub = 1



给我一些记录..比如

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

依此类推,,,



所有这些来自hub = 1



-----------------------------------------

但是我需要在＃facility2中增加一个办公室，其中心不等于1

------------------- -------------------



所以我这样试试



1）

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)

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

- 它给了我错误 - > 4.附近的语法不正确





2）然后我试试这样

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


2)then i tried like this

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

- 其中office4有facility_id = 236，其中心不等于1 ,,,我试过这样。 />
- 我的错误...



- 标识列的显式值只有在使用列列表并且IDENTITY_INSERT为ON时才能指定表'＃facility2'。



3）

然后我尝试了这个

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

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有错误



已有一个名为'＃的对象数据库中的facility2'。



怎么解决这个问题？





4）然后我试着这样......

i got error

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

how to solve this ?


4) then i tried like this ...

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

i有错误



只有在使用列列表并且IDENTITY_INSERT为ON时，才能指定表'＃facility4'中标识列的显式值。







5）我第五次尝试......我刚刚得到解决方案....

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

   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

但怀疑仍然存在， ,,,为什么以上4次尝试都失败,,,,





请帮我这个..

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


Help me regarding this..

推荐答案

1） union all 4，'office4'，如错误所示，是错误的语法。你需要

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

SELECT 4,'office4'

2）我猜你设置 facility_id 带有IDENTITY（1,1）子句 - 这将由DBMS自动生成。仍然可以在插入上定义这些 - 在MSDN中查找 IDENTITY INSERT ON



3）您之前尝试过的数据库中已经有＃facility2 - 在这种情况下，您应首先 DROP 表 - 或者查询的第一部分是创建表和第二部分部分正在生成错误。尝试

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

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

（当您将记录插入表格时，不需要联合）



4）见上文（2）。



5）这是正确的方法 - 通过明确声明 facility_id int，（即没有您可以直接插入数据的IDENTITY子句​​

(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

这篇关于只有在使用列列表且IDENTITY_INSERT为ON时，才能指定表'＃facility2'中标识列的显式值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！