How to insert row into a table by select column name and value from another table in SQL Server

sql sql-server columnname
Snow picture Snow · Sep 29, 2016 · Viewed 7.5k times · Source

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!

Answer

Pரதீப் picture Pரதீப் · Sep 29, 2016

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)

Related questions

Find all stored procedures that reference a specific column in some table

I have a value in a table that was changed unexpectedly. The column in question is CreatedDate: this is set when my item is created, but it's being changed by a stored procedure. Could I write some type of SELECT …

Read More
How to select specific column names in SQL Server

Here is my previous question on how to iterate a string in SQL Server: Now how can I specifically select column names? here is my code for selecting columns: SELECT 'Field '+CAST(ROW_NUMBER() OVER (ORDER BY ordinal_position) …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More