CODEWITHSUNDEEP
sqlsql-server
Rab
Rab

Reputation: 11

SQL Insert Into leaves some columns null

I'm trying to populate a temporary table with average data from another table. The temporary table has 2 columns. Each column of the temporary table should be populated with data from the old table that has a specific value in a column. This is the "tag index" column in the old table and it ranges from 1 to 2. So, I want the value (called val in code) in rows with a tag index of 1 in column 1 of temptable and val in rows with tag index of 2 in column 2.

I'm using INSERT INTO but it rotates inserting null values. My code and output are below and thanks for any help.

Insert INTO #temptable1 (V1)
    SELECT AVG(Val) FROM FloatTable_Z
     WHERE TagIndex=24


Insert Into #temptable1 (V2)
      SELECT AVG(Val) FROM FloatTable_Z
      WHERE TagIndex=26

temptable1

2.06 NULL
NULL 2.08
1.78 NULL
NULL 1.99
''    ''

It repeats the pattern of inserting the value and inserting null for all of my data.

Again thanks for any help or comments!

Upvotes: 1

Views: 1547

Answers (1)

JNevill
JNevill

Reputation: 50034

When you run an INSERT you insert one row. You run two inserts (one inserting into one field, and the other into another field) and you have still inserted two rows. Two inserts, two rows. You can think of it like: INSERT is the CREATE in CRUD (Create, Read, Update, Delete)

A record/row in a SQL table is a bunch of related fields. So if you create a new record and you want to populate another field in that record afterwards (because the new value is related to the old value and they should be in the same record) then you would need to perform an UPDATE statement, not a second INSERT.

However, in this case it would be best to do this in one insert like:

Insert INTO #temptable1 (V1, V2)
SELECT AVG(CASE WHEN TagIndex = 24 THEN VAL END),
    AVG(CASE WHEN TagIndex = 26 THEN VAL END)
FROM FloatTable_Z

Upvotes: 3

Related Questions