0

Hello I am populating a table with data from a different table. These are both pre-existing tables that have data in them. My script is almost working. It selects the appropriate values from the selection table and inserts the new record into the target table, but it is the ID column value that is problematic. I need the ID value to auto-increment based on the previous row. Instead of auto incrementing based on the previous row which is at a value of 268564, the script places a 1 and auto-increment from there as in 1, 2, 3 etc. I need the new row's ID to be 268565. Please see below, thanks all.

set identity_insert Table A ON;
INSERT INTO Table A 
(ID, Field2, Field3, Field4, Field5)

SELECT ROW_NUMBER() OVER (ORDER BY Table A.[ID]) AS Number, Field2, Field3, 0, Field4, Field5

FROM Table B
LEFT JOIN Table A
ON Table A.[match] = Table B.[match]
WHERE 
Table B.[match] = 44593
set identity_insert Table A OFF
ID Field1 Field2 Field3 Field4
268564 table A value1 table A value2 table A value3 table A value4
1 table B value1 table B value2 table B value3 table B value4
tcoady
  • 15
  • 4
  • Please provide some sample data and expected output. – griv Aug 29 '22 at 22:21
  • Why are you not just using the `identity` value? – Stu Aug 29 '22 at 22:21
  • I am not using identity because there are other function that will add additional values to this table. So today the number to increase by one is 268564 but tomorrow it could be at 27000. So I will need the script to constantly check the last value and add one to it then insert the rest of the data for the record. – tcoady Aug 30 '22 at 00:46

1 Answers1

0

Hello all thanks for your help. I was able to figure it out. I needed to use

 ROW_NUMBER() OVER (order by (select 1))+ (SELECT COALESCE(MAX(Table A.[ID]),0) FROM Table A)
tcoady
  • 15
  • 4