How can I insert without changing the identity seed?

Question

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

Answer 1

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.