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!

take a look at http://stackoverflow.com/questions/2005437/an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a — Jande, Nov 15 '16 at 09:51
Just need to copy everything from one server to another for testing purposes so everything will be the same except the database its stored in. The above code is just a create table script from the original table. — Simon, Nov 15 '16 at 09:53

score 4 · Answer 1 · answered Nov 15 '16 at 09:56

4

You can try this:

SET IDENTITY_INSERT TableName ON

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

answered Nov 15 '16 at 09:56

Jande

1,695
2
20
32

What is the TableName here? The taregetTable does not exist in that point. What is it? – ZedZip Aug 14 '17 at 15:11

score 3 · Answer 2 · answered Nov 15 '16 at 09:54

Identity fields by definition won't allow you to insert a value into that column, it will want to name it itself. As the error message states, you can turn IDENTITY_INSERT on to allow this then switch it back off after your insert is completed.

The syntax you'll want is this;

SET IDENTITY_INSERT TableName ON

Not recommend on a production server but if you're just doing this for testing then stick with it.

Your other option is to just make this field an INT and not worry about it automatically creating the identities for you.

score 3 · Answer 3 · answered Jun 02 '17 at 20:14

3

If you do it in SQL Server Management Studio with Tasks->Export Data... There's a button along the way - Edit mappings which shows advanced properties and you can find Enable identity insert checkbox among them.

answered Jun 02 '17 at 20:14

Ivan Leonenko

2,363
2
27
37

Rafael · Answer 4 · 2022-12-01T12:31:43.543

SET IDENTITY_INSERT [MyDataBase].[dbo].[MyTable] ON
INSERT INTO [MyDataBase].[dbo].[MyTable] --[DestinyDataBase].[Schema(exemple: dbo)].[DestinyTable]
    (
    [Id], -- All your columns
    [Name]
    )
    SELECT * FROM [SourceDataBase].[dbo].[SourceTable] --[SourceDataBase].[Schema(exemple: dbo)].[SourceTable]
GO
SET IDENTITY_INSERT [MyDataBase].[dbo].[MyTable] OFF

You must specify explicitly all columns

How to copy one table with identity to another SQL Server

4 Answers4