Insert records into a table with a condition in SQL Server 2016

Question

I have a table that has columns that I want to insert into another table with certain conditions. The closest I could get was using this post.

This is how I want to do it:

if (cnt > 50, avg_account, else null) as avg_account
if (cnt > 50, avg_change, else null) as avg_change

As shown above, I want to insert some columns from Table1 into Table2 and especially avg_account and avg_change columns when cnt column in Table1 is > 50.

I tried the following, I am not getting my desired output using the following code,

SELECT
    id, branch, account, total, change,
    'avg_account' = CASE
                       WHEN cnt > 50 THEN avg_account -- How can I refer to avg_account values?
                       ELSE 'Null'
                    END,
    'avg_change' = CASE
                      WHEN cnt > 50 THEN avg_change  -- How can I refer  to avg_change values?
                      ELSE 'Null'
                   END 
INTO
    Table2
FROM 
    Table1

PRINT '  ' + CONVERT(VARCHAR, @@ROWCOUNT) + ' rows updated'

Is my approach correct? Can I get values as indicated in the code snippet? OR should I use a where clause or subquery?

Any help would be appreciated.

score 1 · Accepted Answer · answered Oct 19 '17 at 18:00

I think this is what you want:

select id, branch, account, total, change,
       (case when cnt > 50 then avg_account end) as avg_account,
       (case when cnt > 50 then avg_change end) as avg_change
into Table2
from Table1;

Notes:

With no else clause, a case expression returns NULL.
NULL and 'NULL' are not the same thing. The latter is a string. The format is probably what you want.
Only use single quotes for string and date constants. Don't use them for column aliases -- because the distinction between a column name and a string is not always obvious.
Don't escape names that do not need to be escaped.
If you want to filter out rows with a cnt <= 50, then use where rather than case.

@i.n.n.m . . . Is there a reason you unaccepted this answer? — Gordon Linoff, Oct 21 '17 at 02:18

score 1 · Answer 2 · answered Oct 19 '17 at 18:01

another approach is to put your condition into where and use two SQLs

 select * into table2 from table1 where cnt > 50

 insert into table2
 select id, branch, account, total, change, null, null from cnt <= 50

Your original query has several bugs

 select      id, branch, account, total, change,
        CASE WHEN cnt > 50 THEN avg_account 
        ELSE 'Null'
        END,
        CASE WHEN cnt > 50 THEN avg_change 
        ELSE 'Null'
        END 
into       Table2
from       Table1

score 0 · Answer 3 · answered Oct 19 '17 at 18:00

0

I think you just need a WHERE clause, like:

INSERT INTO TABLE2
    (id, branch, account, total, change, avg_account, avg_change)
SELECT
    id, branch, account, total, change,
    avg_account, avg_change
FROM
    TABLE1
WHERE
    cnt > 50

I may also me misunderstanding you situation. Can you elaborate a little more?

answered Oct 19 '17 at 18:00

DanielG

1,669
1
12
26

accepted answer is the one I was looking for. Thank you for the effort! – i.n.n.m Oct 19 '17 at 18:49

score 0 · Answer 4 · answered Oct 19 '17 at 18:22

Almost correct. You actually have the logic correct. Your syntax is your problem:

When you enter 'Null' into your database, it will enter the STRING VALUE 'Null', and NOT the NULL Value. Take the quotes off your Nulls.

select id, branch, account, total, change, 'avg_account' = CASE WHEN cnt > 50 THEN avg_account ELSE Null END, 'avg_change' = CASE WHEN cnt > 50 THEN avg_change ELSE Null END into Table2

from Table1 print ' ' + convert(varchar,@@rowcount)+ ' rows updated'

Assuming avg_account and avg_change are columns in table 1.

Insert records into a table with a condition in SQL Server 2016

4 Answers4