0

I will add the records in table A to table B. The tables are the same. However, table B is an actively transactional table. When doing the operation, the record in the tableB will continue to be added row.

tableA
Id
1
2
...
1000000
1500000000
1500000001
1500000002
1500000003

tableB

Id
1000001
1000002
...
80768928

Some of the new records I will add are higher than identity seed, the seed will update to 1500000004. Is there a way to do this without updating this value?

tableB Identity Seed : 80768928

I use these commands when doing the operation:

SET IDENTITY_INSERT tableB ON

INSERT INTO dbo.tableB (...Columns...)
SELECT ...Columns... FROM dbo.tableA

SET IDENTITY_INSERT tableB OFf
3
  • Is using high numbers a problem? Are you close to the max value permitted?
    – EzLo
    Jul 11, 2018 at 13:25
  • @Forrest When I updating the reseed, if a new line is added to the tableB? A new row is added every second and can not be stopped. Jul 11, 2018 at 13:29
  • @EzLo No, column is bigint. But my company has decisions on this issue. Jul 11, 2018 at 13:30

1 Answer 1

0

You could put the old seed into a variable and then reseed the table after the insert.

DECLARE @Seed INT = IDENT_CURRENT( 'tableB' )

SET IDENTITY_INSERT tableB ON

INSERT INTO dbo.tableB (...Columns...)
SELECT ...Columns... FROM dbo.tableA

SET IDENTITY_INSERT tableB OFF

DBCC CHECKIDENT ('tableB', RESEED, @Seed)

This is assuming that no other inserts are going to take place while you do your insert.

2
  • I will transfer 8 million lines. At this time, records will be added to the live table and the seed will be increased. In your case, it will be an old value and duplicate row warning. Jul 12, 2018 at 6:51
  • In that case I agree that my solution would not work. Jul 12, 2018 at 8:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.