Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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