Solved

Apply a function to each row in a table

Posted on 2014-09-11
7
146 Views
Last Modified: 2014-09-12
Hello

I have a function that covnerts IPv6 into IPv4 and I need it to loop through each row in the table to perform the conversion.

The pertinent columns in the proxy table are

[ClientIP] -- this contains the IPv6 entries
[IPv4] -- I added this column in order to host the converted values.

What is the best method to accomplish this? Please note that my TSQL programming is only of a fundamental level!

I have attached the function and I can retrieve an output for single values such as:

SELECT [proxy].[dbo].[fnIpAddressToText] (
   'AC130C0A-FFFF-0000-0000-000000000000')

this obviously only accomplishes one conversion and I need it to loop through all the entries in a relatively large table (hundreds of thousands of rows).

We are using SQL Server 2008R2.

All help appreciated!
0
Comment
Question by:Jay Carax
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40316818
"I have attached the function"

I don't see that attachment
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40316829
Didn't know you can convert IPv6 to IPv4, since you will have much more addresses in IPv6 than IPv4.
I'm curious about how you going to do this (how to handle those not convertible addresses).
0
 

Author Comment

by:Jay Carax
ID: 40316895
Function is now attached.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Jay Carax
ID: 40316905
And Vitor, I've just had a word with the networks guys - it converts a hexedecimal value into IPv4 - apologies for the confusion. The attachment should clear things up.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40316926
Still can't see the attached function but saw the example you posted and by that you can't accomplish what you want, since IPv4 permits at maximum a value of 255 and for example FFFF it's 65535.
0
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40316942
Since you have the existing value as well as the conversion column in the same table, and a separate function to perform the conversion, this should be fairly straightforward:

update yourTable
set IP4V = [proxy].[dbo].[fnIpAddressToText] (ClientIP)

Open in new window


assuming that [proxy].[dbo].[fnIpAddressToText]  is your conversion function.
0
 

Author Closing Comment

by:Jay Carax
ID: 40319484
Exactly what was needed - thank you.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now