Solved

Insert into a different database using where clause on a column

Posted on 2010-08-16
5
428 Views
Last Modified: 2012-08-14
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
Comment
Question by:H-SC
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:lundnak
ID: 33447287
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
 
LVL 7

Expert Comment

by:lundnak
ID: 33447321
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
 
LVL 1

Author Comment

by:H-SC
ID: 33447340
lundnak,

I am not using aliases
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33447516
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
 
LVL 1

Author Comment

by:H-SC
ID: 33448280
angelIII,

Perfect!

many thank you's
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question