Copying data from a table with auto increment column

Question

I have a table in SQL Server with data that has an auto-increment column. The data in the auto increment column is not sequential. It is like 1, 2, 3, 5, 6, 7, 9 (missing 4 and 8).

I want to copy the exact data in this table to another fresh and empty identical table. The destination table also has an auto increment column.

Problem: when I copy the data using the query below, the AttachmentID has new and different values

INSERT INTO FPSDB_new.dbo.Form_Attachment
    SELECT
        CategoryID
    FROM
        FPSDB.dbo.Form_Attachment

the Form_Attachment table in destination and source is same as below

CREATE TABLE [dbo].[Form_Attachment] 
(
    [AttachmentID] [int] IDENTITY(1,1) NOT NULL,
    [CategoryID]   [int] NULL
)

Is there a SQL query solution to make the two tables with identical data?

Answer 1

You can insert into an IDENTITY column by using SET IDENTITY_INSERT ON in your transaction (don't forget to turn it off afterwards):

How to turn IDENTITY_INSERT on and off using SQL Server 2008?

SET IDENTITY_INSERT FPSDB_new.dbo.Form_Attachment ON

INSERT INTO FPSDB_new.dbo.Form_Attachment ( AttachmentID, CategoryID )
SELECT
    AttachmentID,
    CategoryID
FROM
    FPSDB.dbo.Form_Attachment

SET IDENTITY_INSERT FPSDB_new.dbo.Form_Attachment OFF