Combine 2 select counts into 1 insert

[excalibur78]

I have the following I'm trying to get 7 distinct counts from 7 different tables into this temporary table in a single record.

CREATE PROCEDURE [dbo].[Test] AS
CREATE TABLE #Temp
(
UPS int,
SW int,
PCLAN1 int,
PCLAN2 int,
POS int,
SCO1 int,
SCO2 int
)
insert into [#Temp] ([UPS]) select distinct count([store #]) from [ups cutovers] where [finished] = 'True'
insert into [#Temp] ([SW]) select distinct count([store #]) from [store wiring] where [finished] = 'True'
select * from [#temp]
GO


Thanks,


David

 

[JamesLean]

Basic idea is:

INSERT #temp (ups, sw, pclan1, pclan2, pos, sco1, sco2)
VALUES (
  (SELECT COUNT(DISTINCT [store #]) FROM [ups cutovers] WHERE finished = 'true'),
  (SELECT COUNT(DISTINCT [store #]) FROM [store wiring] WHERE finished = 'true'),
  ...
)

--James

 

[excalibur78]

I get "Error 1046: Subqueries are not allowed int his context. Only scalar expressions are allowed."


David

 

[JamesLean]

Oops, sorry. You need to use a SELECT rather than the VALUES clause:

INSERT #temp (ups, sw, pclan1, pclan2, pos, sco1, sco2)
SELECT
  (SELECT COUNT(DISTINCT [store #]) FROM [ups cutovers] WHERE finished = 'true'),
  (SELECT COUNT(DISTINCT [store #]) FROM [store wiring] WHERE finished = 'true'),
  ...

--James

 

[excalibur78]

That works. Your the man!


Thanks,



David