SQL Insert - Msg 207 Invalid column name

Question

The issue is that I am trying to insert some data into my SQL server database, but for some reason I keep getting the infamous "invalid column name" error, and nothing is inserted as a result.

Now, I believe I know WHY (sort of) this is happening, as I am trying to insert a value that is based on the value of a field in another column using a case query, but I'm not sure I'm really doing this right - see the codes:

INSERT INTO dbo.table_name(points, discount)
VALUES
(3, CASE WHEN (points = 6) THEN 0.5 WHEN (points = 12) THEN 1.0 ELSE 0 END

I am getting the invalid column name on the "points" column. It is definitely there so am I correct in thinking that my query is not right, or that I should be going about this a different way?

I am using SQL server 2008 R2 - not sure if that makes any difference.

Solution 1

Number 3 in VALUES section did not yet become 'points' column, so you can't reference it like this in your CASE statement. You could rewrite your query to something like that:

INSERT INTO dbo.table_name(points, discount)
select 
    x.points
    ,CASE WHEN (x.points = 6) THEN 0.5 WHEN (x.points = 12) THEN 1.0 ELSE 0 END as discount
from (
    select 3 as points union all
    select 12
) x

Solution 2

Is this business logic that you want to be able to enforce somehow? If so then it looks to me like you are possibly looking for a calculated column.

CREATE TABLE table_name
( 
    ID INT,
    Points INT,
    Discount AS CASE WHEN (Points = 6) THEN 0.5 WHEN (Points = 12) THEN 1.0 ELSE 0 END
)

This way whatever data you insert into the table will always conform to those rules.

Inserts now become real simple:

INSERT INTO table_name (Points) VALUES(6);
INSERT INTO table_name (Points) VALUES(5);
INSERT INTO table_name (Points) VALUES(12);