How to insert row into a table by select column na

2019-05-17 19:21发布

I have a table with many columns like:

Table A:

ColumnA | ColumnB | ColumnC | ColumnD | ColumnE
_______________________________________________
valueA  | valueB  | valueC  | valueD  | valueE

How can I insert into Table B like below?

Table B:

ColumnName  | ColumnValue
_________________________
ColumnA     | valueA
ColumnB     | valueB
ColumnC     | valueC
ColumnD     | valueD
ColumnE     | valueE

Thank you!

4条回答
Bombasti
2楼-- · 2019-05-17 19:29

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) 
查看更多
霸刀☆藐视天下
3楼-- · 2019-05-17 19:33

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)
查看更多
趁早两清
4楼-- · 2019-05-17 19:37

Use UNPIVOT:

SELECT U.ColumnName,U.ColumnValue
FROM @tblA A
UNPIVOT
(
  ColumnValue
  for ColumnName in ([ColumnA], [ColumnB], [ColumnC],[ColumnD],[ColumnE])
) U;
查看更多
倾城 Initia
5楼-- · 2019-05-17 19:42

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
查看更多
登录 后发表回答