Can't create a temp table with count() in select statement

Question

I am trying to place the output from this select statement into a temp table. The select statement consists only of a string and the aggregate count(). Here is a bit of the code I am trying to use:

SELECT q.* into #tmpClientCounts
    from
    (
    SELECT 'Existing female clients in the program:',
    count([PER_SEX]) as Client_Count ---Count of female clients
    from #tmpClients c --- From another temp table
    -----
    -----bunch of sql that works fine
    -----
    union
    SELECT 'New female clients in the program:',
    count([PER_SEX]) as Client_Count
    from #tmpClients c
    -----
    -----bunch of sql that works fine
    -----
    ) as q

I am doing union between the statements as you can see. This code produces the error shown below (part of the error):

No column name was specified for column 1 of 'q'.

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name

Answer 1

The error message indicates that the issue is with the first column which is just a constant string. Try:

SELECT q.* into #tmpClientCounts
    from
    (
    SELECT 'Existing female clients in the program:' as Header_col,
    count([PER_SEX]) as Client_Count ---Count of female clients
    from #tmpClients c --- From another temp table
    -----
    -----bunch of sql that works fine
    -----
    union
    SELECT 'New female clients in the program:'as Header_col,
    count([PER_SEX]) as Client_Count
    from #tmpClients c
    -----
    -----bunch of sql that works fine
    -----
    ) as q

Notice that the issue is not with your count([PER_SEX]) as Client_Count, but instead with the first column: 'New female clients in the program:'