I have UserSignatures, a table with clientIPs, some of which have CountryIds that are null and need to be filled in.A second table, IpLookups, stores IPs, and a third table Country has the needed CountryId. This is workaround at the moment, because we don't fill CountryIp yet.declare @var1 int,@var2 int,@var3 int,@ind int = 0;SELECT * INTO #TempFROM [dbo].[UserSignatures]where CountryId is nullSELECT @var1 = count(*) FROM #Temp;while (@ind < @var1  )           begin      select @var3 = Id from #Temp order by Id OFFSET @ind ROWS FETCH NEXT 1 ROW ONLY;      select @var2 = CONVERT(BIGINT, PARSENAME(u.ClientIp, 1)) * POWER(256, 0)  + CONVERT(BIGINT, PARSENAME(u.ClientIp, 2)) * POWER(256, 1)  + CONVERT(BIGINT, PARSENAME(u.ClientIp, 3)) * POWER(256, 2)  + CONVERT(BIGINT, PARSENAME(u.ClientIp, 4)) * POWER(256, 3) from [dbo].[UserSignatures] u  where u.Id = @var3      update [dbo].[UserSignatures]        set CountryId = (        SELECT c.Id FROM dbo.IpLookups lo with(NOLOCK)          inner join        [dbo].[Countries] c with(nolock)        on        c.Code = lo.CountryIsoCode         WHERE         @var2          BETWEEN lo.[From] AND lo.[To]        )        where Id = @var3      SET @ind = @ind+1;endDROP table #TempIs there any way to simplify this?
Update user country based on IPv4 decimal address
sql;sql server;ip address
You process data Row By Agonizing Row instead of a single Update statement.There's no need for temp tables, variables and a loop, simply write a single SELECT and use this in an UPDATE FROM. This should return the correct country ids:    SELECT u.id, c.Id     FROM [dbo].[UserSignatures] AS u    JOIN dbo.IpLookups AS lo    ON CONVERT(BIGINT, PARSENAME(u.ClientIp, 1)) * POWER(256, 0)     + CONVERT(BIGINT, PARSENAME(u.ClientIp, 2)) * POWER(256, 1)     + CONVERT(BIGINT, PARSENAME(u.ClientIp, 3)) * POWER(256, 2)     + CONVERT(BIGINT, PARSENAME(u.ClientIp, 4)) * POWER(256, 3)        BETWEEN lo.[FROM] AND lo.[TO]    JOIN [dbo].[Countries] AS c    ON c.Code = lo.CountryIsoCode    WHERE u.CountryId IS NULL