1

I have a table in production DB with ID int identity (1,1). I am trying to add new data to it via SSIS but the table in STG has also the same int identity (1,1) which starts from 1 and I need to start from the max id from the table in DB production. Then I have a merge script that inserts new data upon id How should approach this?

I have try: I have tried to change the store procedure:

CREATE PROCEDURE spmaxid
as 
BEGIN 
    SELECT  max([ID])
FROM [Production].[dbo].[UNITS] 
END

then this:
UPDATE [dbo].[STG UNITS] SET id = NULL
GO 

DECLARE @id INT 
SET @id =  Spmaxid
UPDATE [STG].[dbo].[STG UNITS] 
SET @id = id = @id + 1
GO 

SELECT * FROM [STG UNITS] 
GO
jarlh
  • 42,561
  • 8
  • 45
  • 63
Chris winter
  • 9
  • 2
  • So you just want to start the Identity from your new table starting at max id of other table? You can change the column (or drop it) from your original table and then add it back as identity(@StartingIDFromPrevTable, 1) This starts it at the number you ant, and the second , 1 is how it is incremented for each new row. So it would add 1 to the next row for the ID. – Brad Dec 10 '20 at 15:48
  • Check this out: https://stackoverflow.com/questions/46484008/how-can-i-reset-identity-column-of-my-table-in-sql-server – Brad Dec 10 '20 at 15:49
  • You could just ignore that column when inserting into the production table. Since it's already an identity column it will increment from where it left off. – Tim Mylott Dec 10 '20 at 15:57
  • `DBCC CHECKIDENT ('{your_table}', RESEED, {your_max_id});`. https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15 (check out example C). – critical_error Dec 10 '20 at 17:39
  • Brad I did this: first drop column ID, then : alter table [STG].[dbo].[STG UNITS] add ID INT identity(1,1) DECLARE '@'MaxID INT SELECT '@'MaxID = max(ID) FROM [Production].dbo.UNITS DBCC CHECKIDENT([STG UNITS], RESEED, '@'MaxID) I got Checking identity information: current identity value '65060'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Completion time: 2020-12-14T10:00:19.8493763+00:00 but in the table, I still see identity from 1 and not from maxid of production table What is wrong here – Chris winter Dec 14 '20 at 10:01

1 Answers1

0

my solution was to add to stg a new column called max_id which takes the max id from production +1. then I merged this 2 db's into 1.

Chris winter
  • 9
  • 2