I came across a task of importing my subscriber's list to the autoresponder service. Some of my subscriber's data didn't contain ip address, simply because at the time of collection, the thought of its necessity didn't cross my mind so I decided to populate the list with fake IP addresses. Of course, the task was to make these addresses as little "fake" as possible, meaning they had to be ip address from the Real US range. Since it took some time and effort to figure out how exactly accomplishes it, I thought it would be a good idea to post it, in case someone else might come across the same need. Now I am not claiming that the method I used is absolutely the best one out there, but I think it will be a good place to start. First I looked for a list of US ip ranges. I found one and I am attaching it with this post as a text file. Now if you look at several records from this file it structured as follow: 22.214.171.124/18 126.96.36.199/16 188.8.131.52/24 184.108.40.206/20 220.127.116.11/21 18.104.22.168/21 22.214.171.124/20 126.96.36.199/19 Here the construct before the slash represents an ip address, while as the number after slash represents the variation of the last digit in IP address. In the example of the first records, this means that the entire range of possible IPs is the following: 188.8.131.52/184.108.40.206/220.127.116.11/18.104.22.168/22.214.171.124/126.96.36.199/ 188.8.131.52/184.108.40.206 and so on until it reaches 220.127.116.11 So I imported this text file into Sequel table that I called US_IP_ZONE with the following structure: CREATE TABLE [dbo].[Us_ip_zone]( [ip_start] [varchar](20) NULL, [ip_end] [int] NULL, [done] [bit] NOT NULL CONSTRAINT [DF_Us_ip_zone_done] DEFAULT ((0)) ) ON [PRIMARY] Here the field [ip_start] stored ip address while [ip_end] stored the variation digit. The third [done] had to do with subsequent scan and I will touch up on it later. Now, surly I didn't want to poulate my table with all the ips from the same record sequentually. This would siounds suspicious. Instead decided to take a random number from 0 to 18 (again I am taking the first record as an example) and construct the last digit of ip address based on this selection. For istance if the randome number would be 7 the resulting ip address will be 18.104.22.168, if it would be 12 then 22.214.171.124, if 15 then 126.96.36.199 etc. In terms of sequel statement this objective is achieved with the following query: select @ip_address = substring(ip_start,1,len(ip_start) - charIndex('.',reverse(ip_start))+1)+convert(varchar(3),round(rand()*ip_end,0)) ,@ip_start = ip_start from ( select top 1 * from us_ip_zone where done = 0 ) t Here the internal select separates a top record from the [US_IP_ZONE] table. The external select cuts out the existing address up to the last period and replaces the last digit with the number randomly selected from range. The @ip_start variable populated in order to update the update this record as "used" or "done" in this loop, so that next internal select will give me the next record. Here's how the entire script looks like... declare @ID int declare @ip_address varchar(15) declare @ip_start varchar(15) declare @found bit declare cr cursor for select id from emails_no_ip where recepient_IP is null open cr fetch next from cr into @ID while @@fetch_status = 0 begin select @found = 0 while @found = 0 begin select @ip_address = substring(ip_start,1,len(ip_start) - charIndex('.',reverse(ip_start))+1)+convert(varchar(3),round(rand()*ip_end,0)) ,@ip_start = ip_start from ( select top 1 * from us_ip_zone where done = 0 ) t if exists(select 1 from emails_exported where recepient_IP = @ip_Address) CONTINUE else begin if exists(select 1 from emails_no_ip where recepient_IP = @ip_Address) CONTINUE else begin update emails_no_ip set recepient_IP = @ip_Address where ID = @ID break select @found = 1 end end end update us_ip_zone set done = 1 where ip_start = @ip_start fetch next from cr into @ID end close cr deallocate cr As you can see first I am generating the perspective ip, then I check whether this ip exists among real collected ips in the table [emails_exported]. Then I check whether the perspective ip exists in the table that I attempt to populate [emails_no_ip] to make sure that this ip hasn't been used before. Now in case either table already contains the ip I loop again and generate another perspective ip from the same source record, until the ip is indeed unique. Once uniqueness of perspective IP is ensured the scrip updates the [recepient_IP] field in the target table. The last statement of the loop updates the current record from [US_IP_ZONE] table so that next loop will have a different source IP record to deal with. That's all folks.