CODEWITHSUNDEEP
sql-server
dev_Learning
dev_Learning

Reputation: 13

Insert into with select statement where value is not null

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.

Upvotes: 0

Views: 2017

Answers (1)

sticky bit
sticky bit

Reputation: 37472

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.

Upvotes: 1

Related Questions