• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

Insert into a different database using where clause on a column

I am having trouble with the last item..the where clause in my insert statement.
I keep getting an error of.. "The multi-part  identifier ... could not be bound."
Any ideas greatly appreciated!

INSERT INTO [targetdatabase].[dbo].[mytable]( field1, field2,field3)
SELECT field1, field2, field3
FROM [sourcedatabase].[dbo].[mytable]
where [targetdatabase].[dbo].[mytable].myfield not in(select myfield from sourcedatabase)
0
H-SC
Asked:
H-SC
  • 2
  • 2
1 Solution
 
lundnakCommented:
This error is generally a problem with the syntax of your select statement.
Do you have table aliases defined in the FROM clause?  I double check the table aliases against the fields in the SELECT and WHERE clauses.
0
 
lundnakCommented:
For Example:

Select tbl1.fld1, tbl2.fld1
from table1 as tbl1
join table2 as tbl2 on tbl2.id = tbl1.id

If the fields in the SELECT clause do not have a unique name and the table alias assignment is missing, then you will get this error.
You will also get the error if you misspell a table alias.  (i.e. tbbl2.fld1 instead of tbl2.fld1)

Make sure you check the GROUP BY and ORDER BY clauses also.
0
 
H-SCAuthor Commented:
lundnak,

I am not using aliases
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try
INSERT INTO [targetdatabase].[dbo].[mytable]( field1, field2,field3)
SELECT field1, field2, field3
FROM [sourcedatabase].[dbo].[mytable] t
where not exists ( select null from sourcedatabase s where t.myfield = s.myfield )

Open in new window

0
 
H-SCAuthor Commented:
angelIII,

Perfect!

many thank you's
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now