SQL - moving table data using INSERT INTO - checking values before moving

Question

I'm doing some experimentation with moving values from one table to another using INSERT INTO, and I'm not quite sure how to check the values from the old table (and add a string) before moving it to the new table.

Here are my table definitions

Old_network

user_id | network_id | network_url
2         3            /whatever/something.html

New Network (ideal result)

 user_id | network_id | network_url
 2         3           www.sitename3.com/whatever/something.html

Network

network_id |  network_prefix
1             www.sitename1.com
2             www.sitename2.com
3             www.sitename3.com

So, whats happening here is that I'm using a lookup table for network_id, and adding the network_prefix to [network_url]. If old_network.network_url does not contain a prefix, I need to add it to the beginning of new_network.network_url.

Moving the raw data is the easy part, but I'm stuck on how to validate and add the string if necessary. Here's an outline of the logic:

INSERT INTO dbo.new_network (user_id,network_id,network_url)
SELECT user_id,network_id,network_url FROM old_network    


if old_network.network_id = 1 and network_url like 'www.sitename1.com%'
move it
else
set network_url = 'www.sitename1.com' + network_url

and so on for the other networks in dbo.network

Typing this out and looking at it gave me a little insight, but do I need to declare a temp table? Use parameters? Any ideas are appreciated. Using mssql 2008

Answer 1

You could use a CASE with a regular JOIN to make the decision and insert the correct value. If I understand your problem correctly, this should do it;

INSERT INTO new_network (user_id,network_id,network_url)
 SELECT u.user_id, u.network_id, 
   CASE WHEN u.network_url LIKE s.network_prefix+'%' 
        THEN u.network_url
        ELSE s.network_prefix + '/' + u.network_url END
 FROM url_table u
 JOIN network_table s
 ON u.network_id=s.network_id;

An SQLfiddle to test with.