问题描述
我正在使用触发器将行插入表中,使用 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 [DB*****].[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.