I have three SQL tables as below.
Table #1: Benchmark
WorkLoadType MaxUsersperVCpu vCPU RAM MaxUserCapacity ------------------------------------------------------------ Light 6 2 8 12 Medium 4 4 16 16 Heavy 2 4 16 8 Power 1 6 56 6
Table #2: VMType
id HostPoolName Resource_Group WorkLoadType ------------------------------------------------ 1 Pool1 RG1 Light 2 Pool2 RG2 Light 3 Pool3 RG3 Light 4 Pool4 RG4 Light
Table #3: VM
HostPoolName MemoryInMB Name NumberOfCores Resource_Group VMSize ------------------------------------------------------------------------------- Pool1 8192 VM1 2 RG1 Standard_D2s_v3 Pool1 8192 VM2 2 RG1 Standard_D2s_v3 Pool2 8192 VM3 2 RG2 Standard_D2s_v3 Pool3 8192 VM4 2 RG3 Standard_D2s_v3 Pool3 8192 VM5 2 RG3 Standard_D2s_v3 Pool3 8192 VM6 2 RG3 Standard_D2s_v3 Pool3 8192 VM7 2 RG3 Standard_D2s_v3 Pool3 8192 VM8 2 RG3 Standard_D2s_v3
I need to create a stored procedure which should produce a new table like this. Here Capacity column is the multiplication of MaxUserPervCpu and NumberOfCores
Table: Capacity
Name HostPoolName ResourceGroup WorkLoadType MaxUserPerVCpu NumberOfCores Capacity ----------------------------------------------------------------------------------------------------- VM1 Pool1 RG1 Light 6 2 12 VM2 Pool1 RG1 Light 6 2 12 VM3 Pool2 RG2 Light 6 2 12 VM4 Pool3 RG3 Light 6 2 12 VM5 Pool3 RG3 Light 6 2 12 VM6 Pool3 RG3 Light 6 2 12
A help on this will be highly appreciated.
Advertisement
Answer
This looks like simple joins:
select vm.*, b.* -- choose the columns you want here
from vm join
vmtype vt
on vt.HostPoolName = vm.HostPoolName join
Benchmark b
on b.WorkLoadType = vt.WorkLoadType;