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

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)

      

Thus, the LeftId and RightId fields can contain NULLS or NVARHCAR (even though they are listed as numbers above). They will always be numbers .. only imports created the field as nvarchar (255).

I can change the fields of the original table if needed.

Can anyone please help?

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

Cheers :)

0


a source to share


1 answer


You can do it easily with IsNull:



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

      

+5


a source







All Articles