Inserting records from another query

Question

I am beginner user and have some basic knowledge of the SQL. I have to write a query to select the records from a table and insert into another table. My TABLE1 has almost 180+ columns and TABLE2 has only 6 columns. In TABLE2 first 4 columns are static and will be coming directly from TABLE1. Other 2 columns in TABLE2 are dynamic. They will be populated by each of 180+ columns with column name and column value from TABLE1. Let me explain this using structure if it is not very clear:

>T1:  
>===  
>Col1, Col2, Col3, Col4, Col5,..........,Col175, Col176, Col177, Col178, Col179, Col180
>
>T2:  
>===  
>Col1, Col2, Col3, Col4, NewCol1, NewCol2

So I want a query to select records from T1 and insert them into T2 such that

T2.Col1 = T1.Col1
T2.Col2 = T1.Col2
T2.Col3 = T1.Col3
T2.Col4 = T1.Col4
T2.NewCol1 = Column_Name from T1 (i.e. Col5, Col6, Col7... Col180)
T2.NewCol2 = Column_Value of the column which we are storing in T2.NewCol2

Here are sample:

T1:

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Col8 | Col9 | Col10 | Col11 | Col12

---

Val1 | Val2 | Val3 | Val4 | Val5 | Val6 | Val7 | Val8 | Val9 | Val10 | Val11 | Val12

T2:

Col1 | Col2 | Col3 | Col4 | Col5 | Col6

---

Val1 | Val2 | Val3 | Val4 | Col5 | Val5
Val1 | Val2 | Val3 | Val4 | Col6 | Val6
Val1 | Val2 | Val3 | Val4 | Col7 | Val7
Val1 | Val2 | Val3 | Val4 | Col8 | Val8
Val1 | Val2 | Val3 | Val4 | Col9 | Val9
.
.
Val1 | Val2 | Val3 | Val4 | Col12 | Val12

I want to achieve this in a single dynamic query. Apologies for the formatting and if anything is not clear. Please let me know if you need anything.
Thanks, Sanjay

Answer 1

A little variation on Patrick's approach with UNION ALL.

There's nothing wrong with a union of 100+ subqueries (though eventually you may run into some SQL Server limitations). After all, you do not have the query write the query by hand; you can generate it. You can use a metadata query to do so; this way, T1's actual column names will automatically appear in the generated query. Run the following SQL query in SSMS, with 'Results to Text' selected; the result is Patrick's original query.

SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY COLUMN_NAME) = 1 THEN
           'INSERT INTO T2 (Col1, Col2, Col3, Col4, NewCol1, NewCol2)'
       ELSE
           'UNION ALL'
       END +
       ' SELECT Col1, Col2, Col3, Col4, ''' + COLUMN_NAME + ''', ' + QUOTENAME(COLUMN_NAME) +
       ' FROM T1'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'T1'
AND COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'T2')
ORDER BY COLUMN_NAME

Going one step further, you can use dynamic SQL to combine both steps (retrieve metadata and copy data) in a single SQL statement. In the example below, not only the 'dynamic' column names (Col5-Col180) but also the 'static' column names (Col1-Col4) are retrieved from metadata. Any columns appearing in both tables, are considered 'static'; columns that appear in T1 but not in T2 are considered 'dynamic'. Add WHERE conditions to the metadata queries if you need to exclude certain columns.

-- build up a string of 'static' column names
DECLARE @columnName sysname
DECLARE @columnNames nvarchar(999) = ''
DECLARE staticColumns CURSOR FOR
    SELECT c1.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c1
    INNER JOIN INFORMATION_SCHEMA.COLUMNS c2 ON c2.COLUMN_NAME = c1.COLUMN_NAME
    WHERE c1.TABLE_NAME = 'T1' AND c2.TABLE_NAME = 'T2'
OPEN staticColumns
FETCH NEXT FROM staticColumns INTO @columnName
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @columnNames = @columnNames + QUOTENAME(@columnName) + ', '
    FETCH NEXT FROM staticColumns INTO @columnName
END
CLOSE staticColumns
DEALLOCATE staticColumns

-- for each 'dynamic' column in T1, build and execute an INSERT...SELECT statement    
DECLARE @insertSelect nvarchar(999)
DECLARE dynamicColumns CURSOR FOR
    SELECT 'INSERT INTO T2 (' + @columnNames + 'NewCol1, NewCol2)' +
           ' SELECT ' + @columnNames + '''' + COLUMN_NAME + ''', ' + QUOTENAME(COLUMN_NAME) +
           ' FROM T1'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'T1'
    AND COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'T2')
    ORDER BY COLUMN_NAME
OPEN dynamicColumns
FETCH NEXT FROM dynamicColumns INTO @insertSelect
WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC(@insertSelect)
    FETCH NEXT FROM dynamicColumns INTO @insertSelect
END
CLOSE dynamicColumns
DEALLOCATE dynamicColumns

Answer 2

You could use a union to select all of the items and their values.

select col1
,      col2
,      col3
,      col4
,      'col5' col_name
,      col5 col_value
from   t1
union
all
select col1
,      col2
,      col3
,      col4
,      'col6' col_name
,      col6 col_value
from   t1

... and so on

Another option is a pipelined function:

create type x_rec is object
( col1 varchar2(200)
, col2 varchar2(200)
, col3 varchar2(200)
, col4 varchar2(200)
, col5 varchar2(200)
, col6 varchar2(200)
);
;

create type x_tab is table of x_rec;

create or replace function x
return x_tab
pipelined
is
begin
  for r
  in
  ( select *
    from   y
  )
  loop
    pipe row (r.col1, r.col2, r.col3, r.col4, 'col5', r.col5);
    pipe row (r.col1, r.col2, r.col3, r.col4, 'col6', r.col6);
    -- etc
  end loop;
  --
  return;  
end;

select *
from   table(x)
;