Insert mutiple select queries into a table in SQL Server

Question

I have 3 select queries:

select 
    convert(varchar(50),(CONVERT(varchar(50), enq_id) + '/' + CONVERT(varchar(50), YEAR(fy_year)))) as EnqIDFyYear 
from 
    Sample.dbo.enquiry_details

select 
    A.profile_name 
from 
    profile_profile A, enquiry_details B 
where 
    A.profile_id = B.client_id

select 
    A.group_name 
from 
    mas_group_type A, enquiry_details B 
where 
    A.group_id = B.group_id

I have created a master table:

create table dbo.UltimateTable
(
    ColumnA varchar(max),
    ColumnB varchar(max),
    ColumnC varchar(max)
)

I want to insert the above 3 select queries in the above UltimateTable as its 3 columns.

How can this be done?

I tried something as

insert into dbo.UltimateTable(ColumnA) 
   select 
      convert(varchar(50),(CONVERT(varchar(50), enq_id) + '/'  + CONVERT(varchar(50), YEAR(fy_year)))) as EnqIDFyYear 
   from 
      Sample.dbo.enquiry_details

But it lets me insert one at a time, values other than ColumnA become null.

How to insert all the 3 SQL queries at one go?

EDIT: Maybe I should make the question more clear. The results of the above 3 select queries are as follows:

EnqIDFyYear
1/2015
2/2014

profile_name
ProfileA
ProfileB

group_name
GroupA
GroupB

I want to insert them in UltimateTable like this:

EnqIDFyYear  profile_name  group_name
    1/2015   ProfileA      GroupA
    2/2014   ProfileB      GroupB

Anurag

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Jan 17 '15 at 11:41
@Anurag 1) What if the 3 selects returns different number of rows? 2) What's the relationship between the 3 results? — dario, Jan 17 '15 at 11:48
@king.code: There is no relationship between the 3 selects. Actually I need to bind a gridview with 10 columns coming from 10 different select statements, with different clauses. Hence wanted to make a simple approach in which I map all the 10 diff SQL queries to a master table and bind the master table to the gridview. Can you please help? — Anurag, Jan 17 '15 at 11:53

score 1 · Accepted Answer · edited Jan 26 '17 at 08:29

1

If you really want one column per result line of each statement you could use the UNION functionality:

insert into UltimateTable (Column A, Column B, Column C) 
select 
    convert(varchar(50),(CONVERT(varchar(50), enq_id) + '/' + CONVERT(varchar(50), YEAR(fy_year)))) as EnqIDFyYear, null, null
from 
    Sample.dbo.enquiry_details

UNION

select 
    null, A.profile_name, null
from 
    profile_profile A, enquiry_details B 
where 
    A.profile_id = B.client_id


UNION

select 
     null, null, A.group_name 
from 
    mas_group_type A, enquiry_details B 
where 
    A.group_id = B.group_id

If you want the resultssets to be connected you should use the JOIN syntax:

insert into UltimateTable (Column A, Column B, Column C) 
select 
    convert(varchar(50),(CONVERT(varchar(50),enq_id)+'/'+CONVERT(varchar(50),YEAR(fy_year)))), 
    P.profile_name, 
    G.group_name 
from Sample.dbo.enquiry_details D
join profile_profile P ON P.profile_id=D.client_id
JOIN mas_group_type G ON G.group_id=D.group_id

edited Jan 26 '17 at 08:29

marc_s

732,580
175
1,330
1,459

answered Jan 17 '15 at 11:48

flo

9,713
6
25
41

Edited the question to be more clear. Can you please have a look again? – Anurag Jan 17 '15 at 11:52
Thanks for the solution. It works for now. Actually I have another diff requirement. I need to bind a gridview with 10 columns coming from 10 different select statements, with different clauses. Hence wanted to make a simple approach in which I map all the 10 diff SQL queries to a master table and bind the master table to the gridview. Will you recommend the JOIN method as done by you? There wouldn't be any relationship between the 10 select statements. – Anurag Jan 17 '15 at 11:58
@Anurag How do you decide which result of a single sql-statement is mapped to a row? If you have a clear matching, than you can use that condition in the `ON` clause of the `JOIN`-Syntax. If it is a random matching you may have to use a `CROSS JOIN` (join every row of table one with every row of table2) and reduce to an approriate number. Cation: cross joining 10 tables will produce size(resultset1)*size(resultset(2)*...*size(resultset10) rows! – flo Jan 17 '15 at 12:09
I was able to achieve it. I guess I have a clear matching and hence have used JOIN. Lets see in future. Requirements are not very clear from the client. – Anurag Jan 17 '15 at 12:17

score 0 · Answer 2 · edited May 23 '17 at 12:01

0

insert into dbo.UltimateTable(ColumnA,ColumnB,ColumnC) 
values (select .., select .., select ..)

if there are more than one value per select result, you can use "STUFF" word do concatenate the results into single varchar row. This is already answered in Can I Comma Delimit Multiple Rows Into One Column? and many more questions.

edited May 23 '17 at 12:01

Community

1
1

answered Jan 17 '15 at 11:37

huseyin tugrul buyukisik

11,469
4
45
97

Thanks for replying. Let me try it. – Anurag Jan 17 '15 at 11:41
It does not work Mr. Huseyin. Any other suggestions? – Anurag Jan 17 '15 at 11:45

Insert mutiple select queries into a table in SQL Server

2 Answers2