How to insert variables with a select with multiple conditions SQL Server

How to insert variables with a select with multiple conditions, suppose A function returns a temp table, and i want to populate other table, but what is the query to do something like

INSERT INTO @table (La, Lb, Qaa, Qab,Qbb) 
   SELECT items 
   FROM dbo.SOME_FUNCTION() 
   WHERE Condition for La 
     AND condition for Lb 
     AND condition for Qaa 
     AND condition for Qab 
     AND condition for Qbb...

Well, the code I'm doing with one answer looks like this:

Declare @table TABLE
(
    La   varchar(80),
    Lb   varchar(80),
    Qaa  varchar(80),
    Qab  varchar(80),
    Qbb  varchar(80)
)

INSERT IN开发者_如何学CTO @table (La, Lb, Qaa, Qab,Qbb)
    SELECT  a.La,
            a.Lb,
            a.Qaa,
            a.Qab,
            a.Qbb
    FROM   dbo.function(@somevar) a 
    WHERE   a.itemindex =  1
    AND     a.itemindex =  3 
    AND     a.itemindex =  5 
    AND     a.itemindex =  7 
    AND     a.itemindex =  9 

But I get an error,

Sorry, I corrected it but still getting error

Msg 207, Level 16, State 1, Procedure NLQ_1, Line 189
Invalid column name 'La'.

Why is it wrong, all column names La, Lb, Qaa,...

First, I think the error message is about the La that is being selected from the function (a.La), not about the La that is being inserted to.

Second, there's an issue with the WHERE filter:

WHERE   a.itemindex =  1
AND     a.itemindex =  3 
AND     a.itemindex =  5 
AND     a.itemindex =  7 
AND     a.itemindex =  9 

It is interpreted like this: '[select rows] where a.itemindex is equal to 1 and at the same time it's equal to 3 and, likewise, to 5 and so on.' You would rightfully think that it can never be true.

You were probably thinking about the condition as applied to all rows in general: 'get me these rows and these and these...'. But rather you should think of the condition as applied to every single row. Consider this: 'select every single row where a.itemindex is equal to 1 or it is equal to 3 or to 5 etc.'. That way you would come up with a correct filter condition, specifically this:

WHERE   a.itemindex =  1
OR      a.itemindex =  3 
OR      a.itemindex =  5 
OR      a.itemindex =  7 
OR      a.itemindex =  9 

But you can simplify it even further. How do you like it put this way: 'where a.itemindex is one of the following: 1, 3, 5, 7, 9'? And SQL has a boolean operation for that too, it's called IN ('IN this list:'). And here's what you would eventually get:

WHERE   a.itemindex IN (1, 3, 5, 7, 9)

Marvellous, isn't it?

When declaring a table, just don't use any "as" keywords:

DECLARE @table TABLE
(
    La   varchar(80),
    Lb   varchar(80),
    Qaa  varchar(80),
    Qab  varchar(80),
    Qbb  varchar(80)
)

That's all that's wrong with your code...

Well if I understand you correctly, you need to try something like

INSERT INTO @table (col1, col2, col3, col4, col5)
SELECT  a.col1,
        a.col2,
        a.col3,
        a.col4,
        a.col5
FROM    dbo.Your_Function() a
WHERE   a.colx = condition1
AND     a.coly = condition2
AND     a.colz = condition3

The WHERE logic will be used against the results from the function, and you need to specify the columns you wish to insert to and from.

EDIT:

Declare the variable table as

Declare @table TABLE (     
    La    varchar(80),     
    Lb    varchar(80),     
    Qaa   varchar(80),     
    Qab   varchar(80),     
    Qbb   varchar(80) 
)