使用触发器插入记录时递增 RECORD_ID

2021-09-10 00:00:00 tsql sql-server

我正在使用触发器将行插入表中，使用 INSERT 语句，如下所示，但这样做时 RECORD_ID 编号增加 1 位，因此插入的所有记录都具有相同的编号..

I am using a trigger to insert rows into a table using INSERT statement as below but when doing this the RECORD_ID number increments by 1 digit so all the records inserted have the same number..

这是我用来增加触发器记录的方法.

This is what i'm using to increment the records from the trigger.

, ISNULL((
               SELECT MAX([PROGRESS-RECID]) FROM [DBAdmin].[dbo].[ReTncyTransStatement]
           ),0) + 1 AS [PROGRESS-RECID]

这是我用来加载数据的

;WITH TestTrans   (
       [ORG-CODE]
      ,[TNCY-SYS-REF]
      ,[TRANS-NO]
      ,[POSTING-YEAR]
      ,[POSTING-WEEK]
      ,[TRANS-YEAR]
      ,[TRANS-WEEK]
      ,[TRANS-DATE]
      ,[ACCOUNT-TYPE]
      ,[ACCOUNT-CODE]
      ,[COMMENT]
      ,[TRANS-AMT]
      ,[SOURCE]
      ,[CREATED-USER]
      ,[CREATED-DATE]
      ,[CREATED-TIME]
      ,[UPDATED-USER]
      ,[UPDATED-DATE]
      ,[UPDATED-TIME]
      ,[BATCH-NO]
      ,[BATCH-NO-TYPE]
      ,[SUSPENSE-REF]
      ,[REFERENCE]
      ,[MGT-AREA]
      ,[ANALYSIS-CODE]
      ) 
AS (SELECT   
       [ORG-CODE]
      ,[TNCY-SYS-REF]
      ,[TRANS-NO]
      ,[POSTING-YEAR]
      ,[POSTING-WEEK]
      ,[TRANS-YEAR]
      ,[TRANS-WEEK]
      ,[TRANS-DATE]
      ,[ACCOUNT-TYPE]
      ,[ACCOUNT-CODE]
      ,[COMMENT]
      ,[TRANS-AMT]
      ,[SOURCE]
      ,[CREATED-USER]
      ,[CREATED-DATE]
      ,[CREATED-TIME]
      ,[UPDATED-USER]
      ,[UPDATED-DATE]
      ,[UPDATED-TIME]
      ,[BATCH-NO]
      ,[BATCH-NO-TYPE]
      ,[SUSPENSE-REF]
      ,[REFERENCE]
      ,[MGT-AREA]
      ,[ANALYSIS-CODE] from [SQLViewsPro2Live].[dbo].[RE-TNCY-TRANS] where [TRANS-DATE] between '2019-05-16 00:00:00.000' and '2019-05-17 00:00:00.000'
      )    
INSERT INTO [SQLViewsPro2Test].[dbo].[RE-TNCY-TRANS]   
    SELECT 

     [ORG-CODE]
      ,[TNCY-SYS-REF]
      ,[TRANS-NO]
      ,[POSTING-YEAR]
      ,[POSTING-WEEK]
      ,[TRANS-YEAR]
      ,[TRANS-WEEK]
      ,[TRANS-DATE]
      ,[ACCOUNT-TYPE]
      ,[ACCOUNT-CODE]
      ,[COMMENT]
      ,[TRANS-AMT]
      ,[SOURCE]
      ,[CREATED-USER]
      ,[CREATED-DATE]
      ,[CREATED-TIME]
      ,[UPDATED-USER]
      ,[UPDATED-DATE]
      ,[UPDATED-TIME]
      ,[BATCH-NO]
      ,[BATCH-NO-TYPE]
      ,[SUSPENSE-REF]
      ,[REFERENCE]
      ,[MGT-AREA]
      ,[ANALYSIS-CODE]
   FROM TestTrans;  
GO

感谢任何修复谢谢，

此处提供问题的完整描述:T-SQL:创建触发器以将新列从一个表复制到另一个表并增加 no

Full description of problem available here: T-SQL : create trigger to copy new columns from one table to another and increment no

推荐答案

使 PROGRESS-RECID 成为 IDENTITY 列，它将自动递增.

Make PROGRESS-RECID an IDENTITY column and it will auto-increment.

相关文章