Insert rows not contained in one table to another

Question

I have two nearly identical tables, but for a difference of about 100 rows (out of 150k). I know how to find all the rows that exist in one table but not the other by using

SELECT [Column]
FROM [DB].[dbo].[Table_1] 
WHERE NOT EXISTS 
(SELECT * 
 FROM [DB].[dbo].[Table_1a] 
 WHERE [EDB].[dbo].[Table_1a].[Column] = [DB].[dbo].[Table_1].[Column])

But I want to be able to insert the rows missing from Table_1 into Table_1a, but the above code doesn't work in an insert statement:

INSERT [DB].[dbo].[Table_1] 
SELECT *
FROM [DB].[dbo].[Table_1a]
WHERE NOT EXISTS 
    (SELECT * 
     FROM [DB].[dbo].[Table_1a] 
     WHERE [DB].[dbo].[Table_1a].[Column] = [DB].[dbo].[Table_1].[Column])

As I get the error: The multi-part identifier "DB.dbo.Table_1.Column" could not be bound.

I have looked into that error from The multi-part identifier could not be bound and http://www.sql-server-helper.com/error-messages/msg-4104.aspx but neither of those solve my problem as I am not using JOIN or aliases. So, I am not particularly sure what to do.

I am using SQL Server 2017.

forpas · Accepted Answer · 2018-12-13T21:27:23.927

1

You can get all the rows from [DB].[dbo].[Table_1a] that do not exist in [DB].[dbo].[Table_1a], by using left join:

INSERT [DB].[dbo].[Table_1] 
  SELECT t1a.*
  FROM [DB].[dbo].[Table_1a] t1a
  LEFT JOIN [DB].[dbo].[Table_1] t1
  ON t1a.[Column] = t1.[Column]
  WHERE t1.[Column] IS NULL

edited Dec 13 '18 at 21:27

answered Dec 13 '18 at 21:16

forpas

160,666
10
38
76

Why does the last line `WHERE t1.[Column] IS NULL` need to be there? What does that do? – DrakeMurdoch Dec 14 '18 at 01:13
The `LEFT JOIN` will fetch **all** rows from `[DB].[dbo].[Table_1a]` joined with the matching rows from `[DB].[dbo].[Table_1]`. When there is no matching row in `[DB].[dbo].[Table_1]` (this is what you need) then `t1.[Column]` will be `NULL`. See this for more examples: http://www.sql-join.com/sql-join-types/ – forpas Dec 14 '18 at 08:51

Tab Alleman · Answer 2 · 2018-12-14T15:09:47.280

The problem is you have two FROM clause references to [DB].[dbo].[Table_1a] and you have not aliased them.

So in the WHERE clause of the subquery (which has access to both the inner and outer queries, and therefore to both occurrences of [DB].[dbo].[Table_1a]) the optimizer doesn't know which one you mean here:

WHERE [DB].[dbo].[Table_1a].[Column] = ...

If you give two different aliases to those table references, and pick one in the WHERE clause, you should be fine:

INSERT [DB].[dbo].[Table_1] 
SELECT *
FROM [DB].[dbo].[Table_1a] t1
WHERE NOT EXISTS 
    (SELECT * 
     FROM [DB].[dbo].[Table_1a] t2
     WHERE t2.[Column] = [DB].[dbo].[Table_1].[Column])

EDIT:

Another problem is that you are inserting into Table_1 the records in Table_1a WHERE there does not exist a record in Table_1a that matches a record in Table_1.

This doesn't make much sense. Probably what you meant to do is insert the 1a records that do not already exist in 1. So probably what you meant is this, which works fine:

INSERT INTO Table_1
SELECT * FROM Table_1a t2
WHERE NOT EXISTS(
    SELECT * FROM Table_1 t1
    WHERE t1.Col1=t2.Col1
)

Ok, yes there is a logical flaw in the query you started with, I will edit my answer. — Tab Alleman, Dec 14 '18 at 15:04

Insert rows not contained in one table to another

2 Answers2