webdad3 webdad3 - 4 months ago 17
SQL Question

non-identity column auto increment

I'm working on a project to consolidate data (from 2 different DBs). I have created a table that contains a few columns:


  • MAPPING_ID int

  • ContentID int

  • ContentValue varchar(200)



For Example, when I do my 1st set of inserts against the original data source everything is good.

Mapping_ID: 53
ContentID: 53
ContentValue: Original Data 1

Mapping_ID: 54
ContentID: 54
ContentValue: Original Data 2


But when I do my second set of inserts against the another source (the data I'm trying to merge) I would like the Mapping_ID column to continue to the next number (i.e. 55,56,57...)

I looked at the
row_number
function but that starts at 1. Is there a way to start it at 55?

I suppose I could make that Mapping_ID column an Identity field, but turn it off during the first insert and then seed it with the max value (54) and then turn it on during the second insert.

Is there another way to accomplish this?

Answer

To get row_number() to start at 55, you could just add 54 (or whatever number) to your row_number() calculation:

(row_number() over (order by X partition by Y)) + 54