You need to Unpivot the data. One option uses CROSS APPLY and table valued constructor

SELECT ColumnName,
       ColumnValue
FROM   Yourtable
       CROSS APPLY (VALUES ('ColumnA',ColumnA),
                           ('ColumnB',ColumnB),
                           ('ColumnC',ColumnC),
                           ('ColumnD',ColumnD),
                           ('ColumnE',ColumnE))TC(ColumnName, ColumnValue)

You can also use a normal UNPIVOT for this.

A example using table variables:

declare @TableA table (Id int identity(1,1), ColumnA int, ColumnB int, ColumnC int, ColumnD int, ColumnE int);
declare @TableB table (ColumnName varchar(30), ColumnValue int);

insert into @TableA (ColumnA, ColumnB, ColumnC, ColumnD, ColumnE) values 
(1,2,3,4,5);

insert into @TableB (ColumnName, ColumnValue)
select ColumnName, ColumnValue from @TableA
unpivot (ColumnValue for ColumnName in (ColumnA, ColumnB, ColumnC, ColumnD, ColumnE)) unpiv;

select * from @TableB;

Returns:

ColumnName  ColumnValue
----------  -----------
ColumnA     1
ColumnB     2
ColumnC     3
ColumnD     4
ColumnE     5

Try with dynamic UNPIVOT:

DECLARE @COLS VARCHAR(MAX) = ''
SELECT @COLS = @COLS + ', [' + COLUMN_NAME + ']' FROM [yourDBName].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableA'

DECLARE @ColumNames VARCHAR(MAX)= STUFF(@COLS, 1,1, '')

DECLARE @Cmd VARCHAR(MAX) = '
SELECT * FROM
(SELECT * FROM TableA) x
UNPIVOT
(
    [Value] FOR [Column] IN (' + @ColumNames + ')
) UNPVT'

INSERT INTO TableB
EXEC (@Cmd)

Use UNPIVOT:

SELECT U.ColumnName,U.ColumnValue
FROM @tblA A
UNPIVOT
(
  ColumnValue
  for ColumnName in ([ColumnA], [ColumnB], [ColumnC],[ColumnD],[ColumnE])
) U;