SQL insert same value in a column based on if another column has same values

Question

I have a table called table1:

+----------+----------+--------------+----+
| location | building | buildingcode | id |
+----------+----------+--------------+----+

to which need to do an insert data from a second table2:

+----------+--------------+
| building | buildingcode |
+----------+--------------+
| B1       |           11 |
| B2       |           11 |
| B3       |           22 |
+----------+--------------+

as location is static here, I have the value for location in a temporary variable named @location.

I want to insert @location, building, buildingcode from table2 to table1, but for id column in table1 have a condition which is like if building codes are same, then id values also should be same.

If buildingcodes are different then id values also should be different. Value of id can be taken as the max value of the id column and then increment to 1.

So the sample final output should be like this:

+----------+----------+--------------+----+
| location | building | buildingcode | id |
+----------+----------+--------------+----+
| A        | B1       |           11 |  1 |
| A        | B2       |           11 |  1 |
| A        | B3       |           22 |  2 |
+----------+----------+--------------+----+

How to do this insert operation? Thanks in advance!

Answer 1

I think you should use dense_rank() function (more info here).

From MS docs:

This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

Here is a sample code that should put you on the right track:

declare @table1 table (location char(1), building varchar(50), buildingcode varchar(50), id int)
declare @table2 table (building varchar(50), buildingcode varchar(50))
declare @location char(1)='A'

insert into @table2
values
 ('B1','11')
,('B2','11')
,('B3','22')

insert into @table1
select 
     @location
    , building
    , buildingcode
    , dense_rank() over (order by buildingcode) 
from 
    @table2

select * from @table1

Now table1 contains: