How to copy one table with identity to another SQL Server

Question

I have been asked to copy an existing table to another server but can't seem insert directly when I've re-created the table.

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

Above is the original code and what I was trying to do was copy this and re-create on a different server/database [CHH-BI].[CommDB].

I did this and tried the insert all into the new table from the previous but had the error

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.

Am I going about this the wrong way or missing something? I assumed it was just a straight forward job to create the same table and copy everything over from the old to the new!

Answer 1

You can try this:

SET IDENTITY_INSERT TableName ON

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