Inserting Multiple Select statement in a Temp Table

Question

I have the query below:

Insert into #BidYTDRegions (Code,APAC,EMEA,NAMerica,LAMerica)
    select 'Payroll', Count(*) from DashboardData
    where DataType = 'Bid' and SMHQRegion = 'APAC'  
    and Services like '%Streamline Payroll%',
    Count(*) from DashboardData
    where DataType = 'Bid' and SMHQRegion = 'EMEA'  
    and Services like '%Streamline Payroll%',
    Count(*) from DashboardData
    where DataType = 'Bid' and SMHQRegion = 'N. America'    
    and Services like '%roll%'  ,
    Count(*) from DashboardData
    where DataType = 'Bid' and SMHQRegion = 'L. America'    
    and Services like '%roll%'

I am getting an error Incorrect syntax near ','.

All I am trying to do is insert some data into a temp table based on select statments. Below is my temp table

Create table #BidYTDRegions
(   
  Code nvarchar(50), 
  APAC int, 
  APACRatio nvarchar(20),
  EMEA int, 
  EMEARatio nvarchar(20),
  NAMerica int, 
  NAMericaRatio nvarchar(20),
  LAmerica int, 
  LAmericaRatio nvarchar(20),  
)

S3S · Accepted Answer · 2019-01-17 20:36:27Z

3

It looks like you want subqueries, which would be done like so:

Insert into #BidYTDRegions (Code,APAC,EMEA,NAMerica,LAMerica)
    select 'Payroll'
        ,(select Count(*) from DashboardData
          where DataType = 'Bid' and SMHQRegion = 'APAC'  
          and Services like '%Streamline Payroll%')
        ,(select Count(*) from DashboardData
          where DataType = 'Bid' and SMHQRegion = 'EMEA'  
          and Services like '%Streamline Payroll%')
        ,(select Count(*) from DashboardData
          where DataType = 'Bid' and SMHQRegion = 'N. America'    
          and Services like '%roll%')
        ,(select Count(*) from DashboardData
          where DataType = 'Bid' and SMHQRegion = 'L. America'    
          and Services like '%roll%')

answered Jan 17, 2019 at 20:36

S3S

25.2k5 gold badges29 silver badges48 bronze badges

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

1 Comment

SP1 Over a year ago

That was it..Thank you so much for your time and help.

Gordon Linoff · Accepted Answer · 2019-01-17 20:38:56Z

1

I think you want conditional aggregation:

Insert into #BidYTDRegions (Code, APAC, EMEA, NAMerica, LAMerica)
    select 'Payroll',
            sum(case when SMHQRegion = 'APAC' and Services like '%Streamline Payroll%' then 1 else 0 end),
            sum(case when SMHQRegion = 'EMEA' and Services like '%Streamline Payroll%' then 1 else 0 end),
            sum(case when SMHQRegion = 'N. America' and Services like '%roll%' then 1 else 0 end),
            sum(case when SMHQRegion = 'S. America' and Services like '%roll%' then 1 else 0 end)
    from DashboardData
    where DataType = 'Bid';

It is unclear to me why the Services has a different comparison for different regions. If it were the same, then that condition could be factored out and moved to the WHERE clause along with DataType.

answered Jan 17, 2019 at 20:38

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

SP1 Over a year ago

Thank you so much Gordon..I have noticed you are mostly the first one to help out..Really appreciate it :)

Collectives™ on Stack Overflow

Inserting Multiple Select statement in a Temp Table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related