0

This question already has an answer here:

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

marked as duplicate by hot2use, mustaccio, LowlyDBA, Philᵀᴹ, Max Vernon Jul 13 '18 at 12:28

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Is using high numbers a problem? Are you close to the max value permitted? – EzLo Jul 11 '18 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. – Yunus UYANIK Jul 11 '18 at 13:29
  • @EzLo No, column is bigint. But my company has decisions on this issue. – Yunus UYANIK Jul 11 '18 at 13:30
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.

  • 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. – Yunus UYANIK Jul 12 '18 at 6:51
  • In that case I agree that my solution would not work. – Andrew Lackenby Jul 12 '18 at 8:00

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