CODEWITHSUNDEEP
sql-serverdatabasestored-proceduressql-insertsql-null
maimok
maimok

Reputation: 373

SQL Server - Inserting from one table to another where values are NOT null

I have TABLE A which I am attempting to join it's values into TABLE B. Table B does not allow nullables under column Item. There are a few items in Table A that has a NULL value for item. I am attempting to insert into this table and set a condition to not include the values that are equal to NULL.

Here is an of Table A:

+---+-----------------+
|ID | SizeID |Item    |
+---------------------+
|   |        |        +
| 1 |  22    | Clothing
+---------------------+
| 2 |   2    | Shoes  |
+---------------------+
| 3 |   11   |  NULL  |
+---------------------+
| 4 |   9    |  NULL  |
+---------------------+
| 5 |   10   |  Hats  |
+---+--------+--------+

Table B has the same naming convention of columns. I get an error when running an insert query due to some of the null values and Table B does not allow Nullables. So I attempted to work around this issue by using Where Item <> NULL but when running this query I am having 0 rows affected.

How can I alter my query in order to insert into Table B IF Item is NOT NULL

Here is an example of my query :

INSERT INTO dbo.TableB ([SizeID], [Item])
SELECT(SizeID as SizeID, Item as Item)
FROM dbo.TableA
WHERE Item <> NULL

Upvotes: 0

Views: 2128

Answers (1)

GMB
GMB

Reputation: 222432

This does not do what you think it does:

WHERE Item <> NULL

In SQL, nothing is equal to null - same goes for other comparison operators (not equal, greater than, less than, between, and so on).

You can think of null as undefined, or unknown. Typically null <> null produces a null result (which, in a where clause, behaves identically to false).

To check a value against null, you need special construct is [not] null.

So:

INSERT INTO dbo.TableB (SizeID, Item)
SELECT SizeID, Item
FROM dbo.TableA
WHERE Item IS NOT NULL

Note that I removed the parentheses around the columns in the SELECT clause, as well as the aliases, which are unnecessary.

Upvotes: 1

Related Questions