SQL Query for inserting multi column values in to single column

Question

I have Table "MultiCol" as below

Name LibraryID RegisterID EngineerID
Rahul 1002      4521       4854
Ajay  5072      3151       4833
Vimal 4532      4531       4354

I want to insert the Rahul's all IDs in the "SingleCol" table(shown below) which is having only one Column named "IDS"

So I want the Result as shown below

Table "SingleCol"

Which query pattern will be most efficient in terms of time and space?

marc_s · Accepted Answer · 2010-06-15 18:57:43Z

2

How about this:

INSERT INTO SingleCol(IDS)
   SELECT LibraryID FROM MultiCol WHERE Name = 'Rahul'
   UNION
   SELECT RegisterID FROM MultiCol WHERE Name = 'Rahul'
   UNION
   SELECT EngineerID FROM MultiCol WHERE Name = 'Rahul'

That should grab all three ID's for Rahul and insert them into SingleCol

answered Jun 15, 2010 at 18:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Creasey Over a year ago

don't know if id's can be duplicated, but if so you may need union all

Collectives™ on Stack Overflow

SQL Query for inserting multi column values in to single column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related