Why do I get this error in SQL Server?

Question

I'm write this query:

INSERT INTO Customer_Care_Database_Analysis_Center
   SELECT code_markaz, name_markaz  
   FROM Customer_Care_Database_Analysis_TEMP
   WHERE NOT EXISTS (SELECT 1
                     FROM Customer_Care_Database_Analysis_TEMP ti
                     WHERE ti.code_markaz = Customer_Care_Database_Analysis_Center.code_markaz);

but when I run that query I get this error:

Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "Customer_Care_Database_Analysis_Center.code_markaz" could not be bound.

What happened? How can I solve that?

because you not define this table `Customer_Care_Database_Analysis_Center` in `FROM` — wiretext, Aug 08 '15 at 07:40
You have mentioned Customer_Care_Database_Analysis_TEMP table at both the FROM clause. You need to mention Customer_Care_Database_Analysis_Center table at one place — Upendra Chaudhari, Aug 08 '15 at 07:42

score 1 · Accepted Answer · answered Aug 09 '15 at 16:41

Not sure what's happening with the answers, but I would assume you want to insert the rows that don't exist in the table (field code_markaz) and that you can do this way:

INSERT INTO Customer_Care_Database_Analysis_Center (code_markaz, name_markaz)
SELECT code_markaz, name_markaz  
FROM Customer_Care_Database_Analysis_TEMP tmp
WHERE NOT EXISTS (SELECT 1
      FROM Customer_Care_Database_Analysis_Center c
      WHERE c.code_markaz = tmp.code_markaz)

I also added the columns in the insert statement. If there's only 2 columns in the table it will work without them.

score -1 · Answer 2 · answered Aug 08 '15 at 07:50

The table being inserted into (Customer_Care_Database_Analysis_Center) does not have a column called code_markaz.

Name the target columns:

INSERT INTO Customer_Care_Database_Analysis_Center (some_col_1, some_col_2)
   SELECT code_markaz, name_markaz  
   FROM ...

score -1 · Answer 3 · answered Aug 08 '15 at 07:54

-1

The error comes due to Customer_Care_Database_Analysis_Center table not reference in you not exist inner query.

As above suggested you have to give either alias or join that table.

...FROM Customer_Care_Database_Analysis_TEMP t
   JOIN Customer_Care_Database_Analysis_Center ccdac
    NOT EXISTS (
        SELECT 1
        FROM Customer_Care_Database_Analysis_TEMP ti
        WHERE ti.code_markaz = CCDAC.code_markaz);

answered Aug 08 '15 at 07:54

Ajay2707

5,690
6
40
58

get this errror:Incorrect syntax near the keyword 'NOT'. – behzad razzaqi Aug 08 '15 at 07:56
This has errors. You have to tell what columns to join `ON`. – sam yi Aug 08 '15 at 08:05
@samyi how can i tell that? – behzad razzaqi Aug 08 '15 at 08:05
You cannot go from `JOIN` to `NOT EXISTS`... it's syntactically incorrect. https://msdn.microsoft.com/en-us/library/Bb208894(v=office.12).aspx – sam yi Aug 08 '15 at 08:08
You can use any join based on your requirement to insert, while not exist just ignore the result set. Also @Sam yi, in your link where is written that you will not used both this in a statement. – Ajay2707 Aug 08 '15 at 08:14
Hi. I'm not saying you can't use both. You never completed your JOIN clause because it's missing `ON`. Also, when you're writing correlated subqueries, you have to use a `WHERE` clause before `NOT EXISTS`. – sam yi Aug 08 '15 at 08:18

Why do I get this error in SQL Server?

3 Answers3