CODEWITHSUNDEEP
sql-server
jw11432
jw11432

Reputation: 593

Insert into temp table from SELECT giving strange error

EDIT: I have since been corrected that I need to be doing an update to achieve my intended results. At this point, I'm not able to get the UPDATE statement to do what I want it to, despite my select statement giving me exactly what I want.

begin tran
update #cal1
set calendar_key =
(
SELECT 16801 - 1 + ROW_NUMBER() OVER (
        PARTITION BY 1 ORDER BY CalendarDate
        ) )
FROM #cal1

The results from this give me a repeating 16801 in this column as opposed to going down through the numbers as in my select statement. What am I doing wrong here?

Upvotes: 0

Views: 310

Answers (1)

marc_s
marc_s

Reputation: 754508

Well, the error seems rather clear and obvious: since you're not specifying the Holiday column in your INSERT statement (in the list of columns you're inserting into), no value is being inserted into Holiday - this is stays NULL.

And it appears from the error message that the Holiday does not allow NULL - you need to explicitly provide a value!

INSERT INTO #cal1 (calendar_key, Holiday)
    SELECT 
        16801 - 1 + ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY CalendarDate) AS count,
        -some-value-for-holiday-here-
    FROM #cal1

Other than explicitly providing a value, you could also:

  1. Make the Holiday column nullable, so it would be fine with a NULL
  2. Provide a default constraint for Holiday, so that if you don't provide a value, the defined default will be used instead

UPDATE:

Seeing that you say

The problem here is that the other values are as they should be. Is there not a way I can add data to just this one column?

Do you really want to INSERT a new row?? Or would you much rather want to UPDATE an existing row and set just that calendar_key column's value?? In that case, you need an UPDATE statement - not an INSERT ......

Upvotes: 1

Related Questions