CODEWITHSUNDEEP
sqlsql-serverdatabase
DrakeMurdoch
DrakeMurdoch

Reputation: 859

Insert rows not contained in one table to another

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.

Upvotes: 0

Views: 84

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31775

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
)

Upvotes: 0

forpas
forpas

Reputation: 164069

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

Upvotes: 1

Related Questions