Issue inserting values with a subquery into temporary table SQL Server 2008

Question

Trying to insert values into a temporary table using SQL Server 2008, getting:

Msg 116, Level 16, State 1, Procedure Test_temp_table, Line 274
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

With this query:

VALUES(
(select 
     a.*, b.[formal name], c.*, d.*
 from 
     selecthr20.employee.[career history] a, 
     selecthr20.Employee.[Current Appointments As At Evaluation Date] b,  
     Employee.[BSK Changes in Selected Period] c,
     selecthr20.employee.[career history extra detail] d
 where 
     a.[appointment number] = b.[appointment number]
     and a.[career number] = c.[primary key number]
     and a.[career number] = d.[career number]
     and c.[primary key number] = d.[career number]
     and c.[primary key name] = 'Career Number'
     and b.[person number] in (select b.[person number] 
                               from employee.[current pay as at evaluation date] 
                               where substring([Payroll Name],1,6) = 'DOV020')))

You appear to be using the wrong syntax. INSERT INTO ... SELECT doesn't use the VALUES () expression at all. It's just INSERT INTO #TempTable SELECT ..... — Bacon Bits
– Bacon Bits, Commented Dec 14, 2015 at 16:56
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
– marc_s, Commented Dec 14, 2015 at 17:52

Mike Zalansky · Accepted Answer · 2015-12-14 17:05:20Z

If you are not going to create the temp table first, use select into.

IF OBJECT_ID('TEMPDB.DBO.#TEMP') IS NOT NULL
    DROP TABLE #TEMP;
BEGIN 
    SELECT 
        a.*, 
        b.[formal name], 
        c.*, 
        d.*
    INTO #TEMP
    FROM selecthr20.employee.[career history] a, 
        INNER JOIN selecthr20.Employee.[Current Appointments As At Evaluation Date] b
            ON a.[appointment number] = b.[appointment number]  
        INNER JOIN Employee.[BSK Changes in Selected Period] c
            ON a.[career number] = c.[primary key number]
        INNER JOIN selecthr20.employee.[career history extra detail] d
            ON a.[career number] = d.[career number]
                AND c.[primary key number] = d.[career number]
    where c.[primary key name] = 'Career Number'
    and b.[person number] in (select b.[person number] from employee.[current pay as at evaluation date] where substring([Payroll Name],1,6) = 'DOV020')
END

If you want to create the table first and then insert, here is a template to give you an idea of the structure.

CREATE TABLE #TEMP
(
    <YOURCOLUMNS>
)

INSERT INTO #TEMP
(
    <YOURCOLUMNS>
)
SELECT 
    <YOURCOLUMNS>
FROM selecthr20.employee.[career history] a, 
    INNER JOIN selecthr20.Employee.[Current Appointments As At Evaluation Date] b
        ON a.[appointment number] = b.[appointment number]  
    INNER JOIN Employee.[BSK Changes in Selected Period] c
        ON a.[career number] = c.[primary key number]
    INNER JOIN selecthr20.employee.[career history extra detail] d
        ON a.[career number] = d.[career number]
            AND c.[primary key number] = d.[career number]
where c.[primary key name] = 'Career Number'
and b.[person number] in (select b.[person number] from employee.[current pay as at evaluation date] where substring([Payroll Name],1,6) = 'DOV020')

Siyual · Accepted Answer · 2015-12-14 17:02:29Z

You're using the wrong syntax for an insert from a query result. For this type of insert, you don't need the VALUES() portion.

As a side-note, you should avoid using SELECT * (or, in this case, a.*, c.*, d.*) in your INSERT SELECT statement, as your temp table would have to be set up just right for that insert statement to work, and any changes to the target or source tables would cause that to break.

Your statement should look something like this:

Insert  #YourTempTable
        (List, Your, Columns, Here, ...)
Select  a.*,
        b.[formal name],
        c.*,
        d.*
From    selecthr20.employee.[career history]                                a
Join    selecthr20.Employee.[Current Appointments As At Evaluation Date]    b   On  a.[appointment number] = b.[appointment number]
Join    Employee.[BSK Changes in Selected Period]                           c   On  a.[career number] = c.[primary key number]
Join    selecthr20.employee.[career history extra detail]                   d   On  a.[career number] = d.[career number]
                                                                                And c.[primary key number] = d.[career number]
                                                                                And c.[primary key name] = 'Career Number'
Where   b.[person number] In 
(
    Select  [person number] 
    From    employee.[current pay as at evaluation date] 
    Where   SubString([Payroll Name],1,6) = 'DOV020'
)

Rob · Accepted Answer · 2015-12-15 13:24:44Z

0

Looks like your where clause is also wrong. Remove the column alias 'b'

i.e.

select [person number] 
from employee.[current pay as at evaluation date] 
where substring([Payroll Name],1,6) = 'DOV020')

edited Dec 15, 2015 at 13:24

answered Dec 14, 2015 at 17:02

Rob

4795 silver badges11 bronze badges

Collectives™ on Stack Overflow

Issue inserting values with a subquery into temporary table SQL Server 2008

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related