CODEWITHSUNDEEP
mysqlsql-server
KleberBH
KleberBH

Reputation: 462

SQL query to copy from some columns of a table to one column in another table

I have this scenario

Table 01

reportID | response1 | response2 | response3 | response4 | response5
1        | aaa       | bbb       | ccc       | ddd       | eee
2        | fff       | ggg       | hhh       | iii       | jjj
3        | lll       | mmm       | nnn       | ooo       | ppp
...

And I would like to insert this data into table 02, the result should look like this

id | reportID | response
1  | 1        | aaa
2  | 1        | bbb
3  | 1        | ccc
4  | 1        | ddd
5  | 1        | eee
6  | 2        | fff
7  | 2        | ggg
8  | 2        | hhh
9  | 2        | iii
10 | 2        | jjj
11 | 3        | lll
...

How can I achieve this, I tried:

INSERT INTO table02 (reported, response) 
SELECT reportid, reponse1 FROM table01

But it don't seems right.

Table 01 contains around 4k rows, So the table 2 will have around 20k.

What is the best approach here.

I could create a console application and do it from there, However I would like to do it from SQL Server Management Studio.

Upvotes: 1

Views: 69

Answers (4)

Lucky M
Lucky M

Reputation: 11

When we need to convert columns data into rows, it is called UnPivoting and SQL Server provides a solution for this:

SELECT
  row_number() over (order by (select null)) as id,
  reportID, 
  response
FROM 
  (
    SELECT 
      reportID, 
      response1, 
      response2, 
      response3, 
      response4, 
      response5
   FROM data) d
UNPIVOT
   (response FOR respId IN 
      (response1, response2, response3, response4, response5)
)AS unpvt;

Use this query instead of using Union All.

Upvotes: 0

amahfouz
amahfouz

Reputation: 2398

You should use SELECT INTO. See for instance.

Upvotes: 0

James Z
James Z

Reputation: 12317

To avoid several scans to the table that happen with union all approach, you can also use unpivot to do this:

SELECT
  row_number() over (order by (select null)) as id,
  reportID, 
  response
FROM 
  (
    SELECT 
      reportID, 
      response1, 
      response2, 
      response3, 
      response4, 
      response5
   FROM data) d
UNPIVOT
   (response FOR respId IN 
      (response1, response2, response3, response4, response5)
)AS unpvt;

SQL Fiddle: http://sqlfiddle.com/#!3/9ea669

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The simplest way is to use union all:

insert into table02(reported, response)
    select reportid, reponse1 from table01 union all
    select reportid, reponse2 from table01 union all
    select reportid, reponse3 from table01 union all
    select reportid, reponse4 from table01 union all
    select reportid, reponse5 from table01;

Upvotes: 7

Related Questions