Insert table and prevent duplicates syntax

Question

I have two tables in two different databases holding part of the same information

BigPhoneDB SmallPhoneDB and BigPhoneTBL and SmallPhoneTBL

i am trying to merge the data from SmallPhoneDB.SmallPhoneTBL into BigPhoneDB.BigPhoneTBL while preventing duplicate entries of course

use BigPhoneDB;

Insert into [BigPhoneDB].[BigPhoneTble]( [FirstName],[LastName],[Address],[City],[State],[Zip],[PhoneNumber])

Select [FirstName],[LastName],[Address],[City],[State],[Zip],[PhoneNumber]
From [SmallPhoneDB].[SmallPhoneTBL]

where [SmallPhoneDB].[SmallPhoneTBL] not in ([BigPhoneDB].[BigPhoneTble].[PhoneNumber] )

And I'm getting an error msg that The multi-part identifier "[BigPhoneDB].[BigPhoneTble].[PhoneNumber]" could not be bound.
and i don't understand why.

Any Idea is welcome

Answer 1

You may be better using MERGE command syntax to do the same operation.

Answer 2

Hi，@Debilon

Welcome to Microsoft T-SQL Q&A Forum!

The reason for the failure to bind is: the where filter reference column does not find any location definition, which means that there is a problem with your syntax.
Look carefully, you want to implement data transfer across databases, but the two database schemas are inconsistent. It needs to be specified separately.
Erland gives the way to add the schema.

Secondly, your where syntax is wrong. We usually say that PhoneNumber exists in a certain table in a certain database, but not whether the table in that database contains this field.
Please refer to this document.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Three bright minds answering - and all missing the mistake. How is that possible?

When you make a cross-database reference, you need three components: 1) Database 2) Schema. 3) Table. You are missing the schema. (Whereupon SQL Server interprets the database name as the schema name, and it goes downhill from there.)

Thus, you need:

Insert into [BigPhoneDB].[BigPhoneTble]( [FirstName],[LastName],[Address],[City],[State],[Zip],[PhoneNumber])

should be:

Insert into BigPhoneDB.dbo.BigPhoneTble(FirstName, LastName, Address, City, State, Zip, PhoneNumber)

(And, yeah, I recommend not having brackets all over the place, to prevent you from wearing down your fingers by typing them and your eyes by looking them.)

dbo is the default schema for most users, and it is also possible to say

Insert into BigPhoneDB..BigPhoneTble(FirstName, LastName, Address, City, State, Zip, PhoneNumber)

and SQL Server will fill in dbo in the missing space.

The same applies of course to the rest of the cross-db references in the script as well.

Answer 4

Just to be clear.

You cannot use the target table in your SELECT statement. The SELECT statement is standalone from the INSERT portion.

Answer 5

Insert into [BigPhoneDB].[BigPhoneTble]( [FirstName],[LastName],[Address],[City],[State],[Zip],[PhoneNumber])

Select [FirstName],[LastName],[Address],[City],[State],[Zip],[PhoneNumber]
From [SmallPhoneDB].[SmallPhoneTBL] sp

where sp.[PhoneNumber] not in (Select bp.[PhoneNumber] From [BigPhoneDB].[BigPhoneTble] bp )

Tom