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



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



How can I do this with a Stored Procedure?
IMHO you should use UNION (yes, 19 times)




create table tbl (server_name varchar(100), app1 varchar(100), app2 varchar(100), app3 varchar(100));
insert into tbl values
('server1', 'app xx', 'app xy', 'app yx'),
('server2', 'app xx', 'app xy', null),
('server3', 'app xx', 'app xy', null);
GO



3 rows affected






select server_name, app1 as application from tbl where app1 is not null
union
select server_name, app2 as application from tbl where app2 is not null
union
select server_name, app3 as application from tbl where app3 is not null
GO



server_name | application
:---------- | :----------
server1     | app xx     
server1     | app xy     
server1     | app yx     
server2     | app xx     
server2     | app xy     
server3     | app xx     
server3     | app xy     



dbfiddle here