Need some help trying to do a simple SQL INSERT with some simple data checking.

Question

Hi folks,

I have the following data in a table.

Name                LeftId    RightId
------------------------------------------
Cat                     1  
Cat                     2
Dog                     4
Dog                     5
Dog                               7
Dog                               69
Gerbil                  12        13

I need to insert them into a new table as...

Id         Name
-------------------------------
1          Cat
2          Cat
4          Dog
5          Dog
7          Dog
69         Dog
12         Gerbil    (or 13. don't care which number out of the two)

So the fields LeftId and RightId can contain NULLS OR an NVARHCAR value (even though they are listed as numbers, above.. they will always be numbers .. just the import created the field as nvarchar(255).

I can modify the source table fields, if required.

Can anyone help?

I'm guessing the insert statement will have a select query in it and use a CASE statement. I'm just not sure how to best do that, though.

Cheers :)

Answer 1

You can do this easily with IsNull:

INSERT INTO newtable (id,name)
SELECT IsNull(leftid,rightid), name
FROM oldtable