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?

Community · Accepted Answer · 2017-05-23 12:23:21Z

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, 2017 at 12:23

CommunityBot

11 silver badge

answered Jan 20, 2016 at 7:51

Dai

158k31 gold badges314 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shomaail Over a year ago

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

Dai Over a year ago

@shomaail you will need to DELETE matching rows first. Alternatively look at the MERGE operation.

Fatih Şimşek · Accepted Answer · 2016-01-20 07:54:25Z

1

You can use this command: SET IDENTITY_INSERT to ON

answered Jan 20, 2016 at 7:54

Fatih Şimşek

1552 silver badges11 bronze badges

Comments

marc_s · Accepted Answer · 2016-01-20 09:18:28Z

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, 2016 at 9:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 20, 2016 at 7:54

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Collectives™ on Stack Overflow

Copying data from a table with auto increment column

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related