CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
gbeaven
gbeaven

Reputation: 1790

Adding result of column expression as new column on table variable creation

I'm sure this is really easy but I can't think of a solution and can't seem to find any documentation that answers my exact question.

While inserting values into a table variable how do I set the value of a field to be the result of an expression from another field in the same table?

For example:

declare @tableVar table(
    [col1] int,
    [col2] dec(18,2),
    [col3] dec(18,2)
)
insert into @tableVar
values (100,.03,[col1] * [col2])

select *
from @tableVar

Would ideally return:

col1 col2 col3
100  0.03 3.00

But I get this error instead:

Msg 207, Level 16, State 1, Line 19
Invalid column name 'col1'.

Msg 207, Level 16, State 1, Line 19
Invalid column name 'col2'.

I understand why I get the error I just can't seem to come up with a solution.

Any hints?

Upvotes: 1

Views: 736

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You would use a subquery:

insert into @tableVar (col1, col2, col3)
    select col1, col2, col1 * col2
    from (values (100, 0.03)) v(col1, col2);

Or, better yet, use a computed column:

declare @tableVar table (
    col1 int,
    col2 dec(18, 2),
    col3 as ( convert(dec(18, 2), col1 * col2) )
);

insert into @tableVar (col1, col2)
    values (100, 0.03);

Note that both these examples explicitly list the columns being inserted. That is considered a best-practice.

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need values construct :

insert into @tableVar (col1, col2, col3)
   select col1, col2, col1 * col2
   from (values (100, .03) 
        ) t(col1, col2);

Upvotes: 1

Related Questions