Insert into with select statement where value is not null

Question

I want to perform an insert operation based on the select statement from another table in SQL Server.

I have built this:

INSERT INTO Table1
SELECT  table2.var1, table2.var2, 1, GETDATE(), 1, GETDATE(),0
FROM    table2

The values in Table1 are all NOT NULL, and there is a couple of record of table2.var2 where there is a null value, I want to skip the Null and continue with the operation.

sticky bit · Accepted Answer · 2020-07-08 22:16:14Z

1

You can filter the rows where table2.var2 is null in a WHERE clause.

INSERT INTO table1
            (<list of target columns>)
            SELECT table2.var1,
                   table2.var2,
                   1,
                   getdate(),
                   1,
                   getdate(),
                   0
                   FROM table2
                   WHERE table2.var2 IS NOT NULL;

You should also explicitly list the targeted columns in any INSERT so that statements don't break if the number or order of columns change in the target table.

answered Jul 8, 2020 at 22:16

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert into with select statement where value is not null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related