sql insert from table to table

Question

I have a table Farm with these columns

FarmID:(primary)
Kenizy:
BarBedo:
BarBodo:
MorKodo:

These columns are palm types in some language. each column of those contains a number indicates the number of this type of palm inside a farm.

Example:

FarmID  | Kenizy  | BarBedo | BarBodo | MorKodo
-----------------------------------------------
3       |   20    |   12    |   45    |   60
22      |   21    |    9    |    41   |   3

I want to insert that table into the following tables:

Table Palm_Farm

FarmID:(primary)
PalmID;(primary)
PalmTypeName:
Count:

That table connects each farm with each palm type.

Example:

FarmID   |   PalmID   |  PalmTypeName   |  Count
-----------------------------------------------
   3     |     1      |      Kenizy     |   20
   3     |     2      |    BarBedo      |  12
   3     |     3      |     BarBodo     |  45
   3     |     4      |     MorKodo     |  60
  22     |     1      |     Kenizy      |  21
  22     |     2      |     BarBedo     |  9
  22     |     3      |     BarBodo    |   41
  22     |     4      |     MorKodo    |   3

I have to use the following table Palms in order to take the PalmID column.

PalmID:(primary)
PlamTypeName:
...other not important columns

This table is to save information about each palm type.

Example:

PalmID  |    PlamTypeName 
-------------------------
   1    |  Kenizy     
   2    |  BarBedo     
   3    |  BarBodo       
   4    |  MorKodo    

The PalmTypeName column has the value the same as the COLUMN NAMES in the Farm table.

So my question is:

How to insert the data from Farm table to Palm_Farm considering that the PalmID exist in the Palm table

I hope I could make my question clear, I tried to solve my problem myself but the fact that the column name in the Farm table must be the column value in the Palm_Farm table couldn't know how to do it.

I can't change the table structure because we are trying to help a customer with this already existing tables

I am using SQL Server 2008 so Merge is welcomed.

Update

After the genius answer by @GarethD, I got this exception

Answer 1

You can use UNPIVOT to turn the columns into rows:

INSERT Palm_Farm (FarmID, PalmID, PalmTypeName, [Count])
SELECT  upvt.FarmID,
        p.PalmID,
        p.PalmTypeName,
        upvt.[Count]
FROM    Farm AS f
        UNPIVOT
        (   [Count]
            FOR PalmTypeName IN ([Kenizy], [BarBedo], [BarBodo], [MorKodo])
        ) AS upvt
        INNER JOIN Palms AS p
            ON p.PalmTypeName = upvt.PalmTypeName;

Example on SQL Fiddle

The docs for UNPIVOT state:

UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. Suppose the table produced in the previous example is stored in the database as pvt, and you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, and Emp5 into row values that correspond to a particular vendor. This means that you must identify two additional columns. The column that will contain the column values that you are rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently reside under the columns being rotated will be called Orders. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition.

To explain further how unpivot works, I will look at the first row original table:

FarmID  | Kenizy  | BarBedo | BarBodo | MorKodo
-----------------------------------------------
3       |   20    |   12    |   45    |   60

So what UPIVOT will do is look for columns specified in the UNPIVOT statement, and create a row for each column:

SELECT  upvt.FarmID, upvt.PalmTypeName, upvt.[Count]
FROM    Farm AS f
        UNPIVOT
        (   [Count]
            FOR PalmTypeName IN ([Kenizy], [BarBedo])
        ) AS upvt;

So here you are saying, for every row find the columns [Kenizy] and [BarBedo] and create a row for each, then for each of these rows create a new column called PalmTypeName that will contain the column name used, then put the value of that column into a new column called [Count]. Giving a result of:

FarmID  | Kenizy  | Count |
---------------------------
3       | Kenizy  |  20   | 
3       | BarBedo |  12   |

---

If you are running SQL Server 2000, or a later version with a lower compatibility level, then you may need to use a different query:

INSERT Palm_Farm (FarmID, PalmID, PalmTypeName, [Count])
SELECT  f.FarmID,
        p.PalmID,
        p.PalmTypeName,
        [Count] = CASE upvt.PalmTypeName
                    WHEN 'Kenizy' THEN f.Kenizy
                    WHEN 'BarBedo' THEN f.BarBedo
                    WHEN 'BarBodo' THEN f.BarBodo
                    WHEN 'MorKodo' THEN f.MorKodo
                END
FROM    Farm AS f
        CROSS JOIN
        (   SELECT  PalmTypeName = 'Kenizy' UNION ALL
            SELECT  PalmTypeName = 'BarBedo' UNION ALL
            SELECT  PalmTypeName = 'BarBodo' UNION ALL
            SELECT  PalmTypeName = 'MorKodo'
        ) AS upvt
        INNER JOIN Palms AS p
            ON p.PalmTypeName = upvt.PalmTypeName;

This is similar, but you have to create the additional rows yourself using UNION ALL inside the subquery upvt, then choose the value for [Count] using a case expression.

---

To update when the row exists you can use MERGE

WITH Data AS
(   SELECT  upvt.FarmID,
            p.PalmID,
            p.PalmTypeName,
            upvt.[Count]
    FROM    Farm AS f
            UNPIVOT
            (   [Count]
                FOR PalmTypeName IN ([Kenizy], [BarBedo], [BarBodo], [MorKodo])
            ) AS upvt
            INNER JOIN Palms AS p
                ON p.PalmTypeName = upvt.PalmTypeName
)
MERGE Palm_Farm WITH (HOLDLOCK) AS pf
USING Data AS d
    ON d.FarmID = pf.FarmID
    AND d.PalmID = pf.PalmID
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (FarmID, PalmID, PalmTypeName, [Count])
    VALUES (d.FarmID, d.PalmID, d.PalmTypeName, d.[Count])
WHEN MATCHED THEN 
    UPDATE
    SET [Count] = d.[Count],
        PalmTypeName = d.PalmTypeName;