Sql-server – Stored Procedure to insert data

sql serverstored-procedurestable

Table1 contains ServerName and a list of applications (from Application01 to Application20) installed on the server.

Table 1

Now I need to import data from Table1 to Table2. The final Table2 should look as following:

Table2

How can I do this with a Stored Procedure?

Best Answer

If you're feeling adventurous, give UNPIVOT a try:

--Setup demo data
DECLARE @T TABLE (
    ServerName VARCHAR(20)
    ,Application01 VARCHAR(20)
    ,Application02 VARCHAR(20)
    ,Application03 VARCHAR(20)
    ,Application04 VARCHAR(20)
    ,Application05 VARCHAR(20)
    ,Application06 VARCHAR(20)
    ,Application07 VARCHAR(20)
    ,Application08 VARCHAR(20)
    ,Application09 VARCHAR(20)
    ,Application10 VARCHAR(20)
    ,Application11 VARCHAR(20)
    ,Application12 VARCHAR(20)
    ,Application13 VARCHAR(20)
    ,Application14 VARCHAR(20)
    ,Application15 VARCHAR(20)
    ,Application16 VARCHAR(20)
    ,Application17 VARCHAR(20)
    ,Application18 VARCHAR(20)
    ,Application19 VARCHAR(20)
    ,Application20 VARCHAR(20)
    )

INSERT INTO @T (ServerName,Application01,Application02)
VALUES ('Server1','ApplicationXY','ApplicationXX')
    ,('Server2','ApplicationZZ','ApplicationYY')
    ,('Server3','ApplicationXZ','ApplicationZX');

;

--The actual code
WITH _cte
AS (
    SELECT ServerName
        ,ApplicationValue
    FROM (
        SELECT *
        FROM @T
        ) AS t
    UNPIVOT(Applicationvalue FOR Application IN (
                Application01
                ,Application02
                ,Application03
                ,Application04
                ,Application05
                ,Application06
                ,Application07
                ,Application08
                ,Application09
                ,Application10
                ,Application11
                ,Application12
                ,Application13
                ,Application14
                ,Application15
                ,Application16
                ,Application17
                ,Application18
                ,Application19
                ,Application20
                )) AS ApplicationValue
    )
--INSERT INTO <TargetTable>(ServerName, Application)
SELECT ServerName, Applicationvalue
FROM _cte
WHERE Applicationvalue IS NOT NULL

| ServerName | Applicationvalue |
|------------|------------------|
| Server1    | ApplicationXY    |
| Server1    | ApplicationXX    |
| Server2    | ApplicationZZ    |
| Server2    | ApplicationYY    |
| Server3    | ApplicationXZ    |
| Server3    | ApplicationZX    |
Related Question