插入數據的儲存過程

表 1 包含伺服器名稱和安裝在伺服器上的應用程序列表（從 Application01 到 Application20）。

現在我需要將數據從 Table1 導入到 Table2。最終的 Table2 應如下所示：

如何使用儲存過程做到這一點？

如果您喜歡冒險，請UNPIVOT嘗試一下：

--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    |

引用自：https://dba.stackexchange.com/questions/204078