sql-server - 如何将一个具有标识的表复制到另一台 SQL Server

Question

我被要求将现有表复制到另一台服务器，但当我重新创建表时似乎无法直接插入。

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

我这样做了，并尝试将上一个表中的所有内容插入到新表中，但出现错误

Msg 8101, Level 16, State 1, Line 4
An explicit value for the identity column in table 'CommDB.dbo.tblRadiologyData' can only be specified when a column list is used and IDENTITY_INSERT is ON.

我是否以错误的方式处理这个问题或者遗漏了什么？我认为创建同一个表并将所有内容从旧表复制到新表只是一项直接的工作!

Answer 1

你可以试试这个:

SET IDENTITY_INSERT TableName ON

SELECT * INTO targetTable 
FROM [sourceserver].[sourcedatabase].[dbo].[sourceTable]