CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Greg Gum
Greg Gum

Reputation: 37885

Insert items missing from TableB Into TableB based on items existing in TableA

I have two tables: TableA - a list of default items. TableB is a copy of TableA, but may have items missing. I need an insert statement that inserts the missing items into TableB

TableA

Key varchar(10)

Rows:
Key1
Key2

TableB 
Key varchar(10)

Rows:
Key1

How to Select all missing items from TableA and insert into TableB? In other words, insert Key2 into TableB.

Using Sql Server 2008R2.

Upvotes: 0

Views: 36

Answers (2)

EricZ
EricZ

Reputation: 6205

You can use LEFT JOIN

INSERT INTO TableB ([key])
SEECT a.[key]
FROM TableA a
LEFT JOIN TableB b
 ON a.[key] = b.[key]
WHERE b.[key] IS NULL

Upvotes: 1

orgtigger
orgtigger

Reputation: 4122

This should go though TableB and overwrite any blank or null items in key2 based on what is in tableA (if tableA has blanks or nulls that would be a different story)

update TableB b
set b.key2 case b.key2
    when ' ' then
        select key2 from tableA where a.key1 = b.key1
    when null then
        select key2 from tableA where a.key1 = b.key1

Upvotes: 0

Related Questions