我被要求将现有表复制到另一个服务器,但在重新创建该表后,似乎无法直接插入。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblRadiologyData]
(
[RadiologyID] [int] IDENTITY(1,1) NOT NULL,
[ExaminationDate] [datetime] NOT NULL,
[ReferralDate] [datetime] NULL,
[ReportedDate] [datetime] NULL,
[AttendanceNumber] [varchar](10) NULL,
[LocalPatientIdentifier] [varchar](10) NOT NULL,
[NHSNumber] [varchar](10) NULL,
[Surname] [varchar](35) NULL,
[Forenames] [varchar](35) NULL,
[DateOfBirth] [datetime] NULL,
[AttendanceStatus] [varchar](20) NULL,
[AttendancePatientCategory] [varchar](10) NULL,
[AttendancePatientGroup] [varchar](20) NULL,
[AttendanceSpecialtyName] [varchar](50) NULL,
[AttendancePriority] [varchar](10) NULL,
[AttendanceSiteCode] [varchar](4) NULL,
[ExamExaminationCode] [varchar](10) NOT NULL,
[ExamRoomName] [varchar](50) NULL,
[ExamExaminationName] [varchar](30) NULL,
[ExamKornerCategory] [varchar](10) NULL,
[KornerBandName] [varchar](20) NULL,
[AttendanceSourceName] [varchar](30) NULL,
[RefDoctor] [varchar](30) NULL,
[DemogRegisteredGPCode] [varchar](8) NULL,
[RegPracCode] [varchar](10) NULL,
[Practice] [varchar](6) NULL,
[DOHCode] [varchar](8) NULL,
[PCOCode] [varchar](5) NULL,
[ExamDuration] [int] NULL,
[InternalNumber] [varchar](12) NULL,
[Postcode] [varchar](8) NULL,
[PCTRes] [varchar](5) NULL,
[DHA] [varchar](3) NULL,
[KornerBand] [varchar](2) NULL,
[OPUnbundled] [bit] NOT NULL,
[UB_HRG] [varchar](5) NULL,
[StatusCode] [varchar](2) NULL,
[LastModified] [datetime] NULL,
[SpecialtyCode] [varchar](3) NULL,
[deptcode] [varchar](255) NULL,
[HRGCode] [varchar](5) NULL,
[HRGGroup] [varchar](6) NULL,
[HRGTariff] [decimal](19, 4) NULL,
[Chargeable] [bit] NOT NULL,
[HEYActivity] [varchar](10) NULL,
[InternallyTraded] [varchar](3) NULL,
[PatientSex] [nchar](10) NULL,
[EthnicCategory] [nchar](10) NULL,
[AgeAtExamDate] [int] NULL,
[HRGCode1516] [varchar](5) NULL,
CONSTRAINT [PK_tblRadiologyData]
PRIMARY KEY NONCLUSTERED ([ExaminationDate] ASC,
[LocalPatientIdentifier] ASC,
[ExamExaminationCode] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblRadiologyData]
ADD DEFAULT ((0)) FOR [OPUnbundled]
GO
ALTER TABLE [dbo].[tblRadiologyData]
ADD DEFAULT ((0)) FOR [Chargeable]
GO
上面是原始代码,我尝试做的是复制它并在不同的服务器/数据库[CHH-BI].[CommDB]上重新创建。
我这样做了,并尝试从以前的表全部插入到新表中,但出现错误
消息8101,级别16,状态1,第4行
只有在使用了列列表并且IDENTITY_INSERT为ON时,才能为表“CommDB.dbo.tblRadiologyData”中的标识列指定显式值。
我是走错了路还是遗漏了什么?我以为这只是一个直接的工作,创建相同的表,并将所有内容从旧的复制到新的!
4条答案
按热度按时间4ngedf3f1#
根据定义,标识字段不允许您将值插入到该列中,它将希望为它自己命名。正如错误消息所述,您可以打开
IDENTITY_INSERT
以允许此操作,然后在插入完成后将其关闭。您需要的语法如下所示;
不建议在生产服务器上使用,但如果您只是为了测试而这样做,请坚持使用它。
您的另一个选择是只使此字段为INT,而不用担心它会自动为您创建身份。
bhmjp9jg2#
您可以尝试以下操作:
tzxcd3kk3#
如果您在SQL Server Management Studio中使用“任务”-〉“导出数据...”执行此操作时会出现一个按钮-
Edit mappings
,它显示高级属性,您可以在其中找到Enable identity insert
复选框。lymnna714#
You must specify explicitly all columns