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?

score 8 · Accepted Answer · edited May 23 '17 at 12:23

8

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

edited May 23 '17 at 12:23

Community

1
1

answered Jan 20 '16 at 07:51

Dai

141,631
28
261
374

your answer is correct but if I have data inside the Destination table and I want to replace the rows with that of Source table. Then how can I update the rows of the destination table – Shomaail Jan 21 '16 at 08:40
@shomaail you will need to `DELETE` matching rows first. Alternatively look at the `MERGE` operation. – Dai Jan 21 '16 at 15:39

score 1 · Answer 2 · edited Jan 20 '16 at 09:18

1

You can also do this:

Drop the copy table
Create as select, which will copy the exact structure and data to the new table.
```
Select * 
into new_table  
from old_table 
```

edited Jan 20 '16 at 09:18

marc_s

732,580
175
1,330
1,459

answered Jan 20 '16 at 07:54

sagi

40,026
6
59
84

score 1 · Answer 3 · answered Jan 20 '16 at 07:54

1

You can use this command: SET IDENTITY_INSERT to ON

answered Jan 20 '16 at 07:54

Fatih Şimşek

155
1
10

Copying data from a table with auto increment column

3 Answers3