老是报:仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'test2.dbo.enterprise_basicInfo' 中为标识列指定显式值。
可是我加了 SET IDENTITY_INSERT test2.dbo.enterprise_basicInfo ON了呀,为什么?
use test2
drop TABLE enterprise_basicInfo
CREATE TABLE [enterprise_basicInfo] (
[enterprise_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[enterprise_name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[enterprise_code] [varchar] (21) COLLATE Chinese_PRC_CI_AS NULL ,
[enroll_no] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
[address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[sub_obj_id] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[etps_type_id] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[legal_man] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[capital] [money] NULL ,
[money_type] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_enterprise_basicInfo_money_type] DEFAULT ('002'),
[range_branch] [varchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[telephone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[contact_man] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[postcode] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[bg_date] [datetime] NULL ,
[license_star_date] [datetime] NULL ,
[license_end_date] [datetime] NULL ,
[status_id] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[newEnterprise_stat] [int] NOT NULL CONSTRAINT [DF_enterprise_basicInfo_newEnterprise_stat] DEFAULT (0),
[card] [bit] NULL ,
[neighborhood_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[trd_type_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Zone_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Real_address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Room_area] [numeric](18, 0) NULL ,
[Organization_code] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[accpt_prgan_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[rpl_date] [datetime] NULL ,
[start_date] [datetime] NULL ,
[end_date] [datetime] NULL ,
[Tax_id] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
[Tax_inner_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[created_date] [datetime] NULL ,
[updated_date] [datetime] NULL ,
[from_organ] [int] NULL CONSTRAINT [DF__enterpris__from___0AF29B96] DEFAULT (0),
[range_type] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[has_compared] [bit] NULL ,
CONSTRAINT [PK_enterprise_basicInfo] PRIMARY KEY CLUSTERED
(
[enterprise_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--SET IDENTITY_INSERT test1.dbo.enterprise_basicInfo OFF
SET IDENTITY_INSERT test2.dbo.enterprise_basicInfo ON
GO
insert test2.dbo.enterprise_basicInfo select * from test1.dbo.enterprise_basicInfo
GO
可是我加了 SET IDENTITY_INSERT test2.dbo.enterprise_basicInfo ON了呀,为什么?
use test2
drop TABLE enterprise_basicInfo
CREATE TABLE [enterprise_basicInfo] (
[enterprise_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[enterprise_name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[enterprise_code] [varchar] (21) COLLATE Chinese_PRC_CI_AS NULL ,
[enroll_no] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
[address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[sub_obj_id] [varchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[etps_type_id] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[legal_man] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[capital] [money] NULL ,
[money_type] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_enterprise_basicInfo_money_type] DEFAULT ('002'),
[range_branch] [varchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[telephone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[contact_man] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[postcode] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[bg_date] [datetime] NULL ,
[license_star_date] [datetime] NULL ,
[license_end_date] [datetime] NULL ,
[status_id] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[newEnterprise_stat] [int] NOT NULL CONSTRAINT [DF_enterprise_basicInfo_newEnterprise_stat] DEFAULT (0),
[card] [bit] NULL ,
[neighborhood_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[trd_type_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Zone_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Real_address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Room_area] [numeric](18, 0) NULL ,
[Organization_code] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[accpt_prgan_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[rpl_date] [datetime] NULL ,
[start_date] [datetime] NULL ,
[end_date] [datetime] NULL ,
[Tax_id] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
[Tax_inner_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[created_date] [datetime] NULL ,
[updated_date] [datetime] NULL ,
[from_organ] [int] NULL CONSTRAINT [DF__enterpris__from___0AF29B96] DEFAULT (0),
[range_type] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[has_compared] [bit] NULL ,
CONSTRAINT [PK_enterprise_basicInfo] PRIMARY KEY CLUSTERED
(
[enterprise_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--SET IDENTITY_INSERT test1.dbo.enterprise_basicInfo OFF
SET IDENTITY_INSERT test2.dbo.enterprise_basicInfo ON
GO
insert test2.dbo.enterprise_basicInfo select * from test1.dbo.enterprise_basicInfo
GO
GO
insert test2.dbo.enterprise_basicInfo select * from test1.dbo.enterprise_basicInfo
GO因为你用GO 关键字把两句隔开了 不是同一会画环境下,那么设置会不成功的
把多余的哪个GO去掉
SET IDENTITY_INSERT test2.dbo.enterprise_basicInfo ON
insert test2.dbo.enterprise_basicInfo select * from test1.dbo.enterprise_basicInfo
GO
--------------
表述有误 应该是: 作用不到你的insert 语句的会话环境
drop TABLE enterprise_basicInfoGO
........
完全不当"SET IDENTITY_INSERT test2.dbo.enterprise_basicInfo ON"这句话存在嘛!!!
SET IDENTITY_INSERT 表 ON
GO-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO