I am trying to insert the column "Judicial_Comments" from a table called "Judicial_Comments" into Column "Judicial_Comments" in a table called "Loan_Info_local". The part that is giving me the error is in the WHERE clause

The error I'm getting:
"Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "dbo.Loan_Info_local.TS" could not be bound."

CODE:
USE ClientDB_MASTER

INSERT INTO Loan_Info_Local (TS, Judicial_Comments)
SELECT TS, Judicial_Comments
FROM Judicial_Comments
Where Judicial_Comments.[TS]=Loan_Info_local.TS

This might be the cause of your problem -- you are using a column with the same name as the table -- this is bound to confuse the query parser. I would strongly suggest renaming the field or the table if possible.

Mike

The problem is that you can't reference a field in a table in a WHERE clause like that. That's where the error message comes in. You need to use a DLookup or something else, to pull the value you need to filer the SELECT clause of your APPEND query.

If this returns the correct records:

SELECT j.TS, j.Judicial_Comments
FROM Judicial_Comments jc
INNER JOIN Loan_Info_local l j.TS = l.TS

...then I would think this would work for the INSERT:

INSERT INTO Loan_Info_Local (TS, Judicial_Comments)
SELECT j.TS, j.Judicial_Comments
FROM Judicial_Comments jc
INNER JOIN Loan_Info_local l j.TS = l.TS

Have to wonder whether that's really what David wants though, Walter.

That would create a second set of rows in Loan_Info_Local with the same value for TS.

Sounds as though he may need an UPDATE query, not an INSERT INTO query!

You may be right ... I was just going by his initial comments:

>>trying to insert the column "Judicial_Comments"<<

...and the fact he was using an INSERT query to do the work.

??

Actually, that was the phrase that caught my attention too. As you know, many people think that INSERT works on columns when it only works on rows.

Thank you everyone for the great comments. They've been very informative.

To clear things up for everyone, I created a column "Judicial_Comments" in the Loan_Info_local table, which is completely blank/null currently. The data I have in the Judicial_comments table I was to copy over into the loan_Info_local table in the column "Judicial_Comments"


**I did change the name of my table from Judicial_Comments to Judicial, but I'm still having problems...I believe I do need an update query.
So far my code is:
USE ClientDB_MASTER

Update Loan_Info_local
SET Judicial_Comments= Judicial.Judicial_Comments
WHERE Judicial.[TS] = loan_Info_local.TS

currently I am getting an error that "The multi-part identifier "Judicial.TS" could not be bound."

Any help writing this would be greatly appreciated, thank you!

This post has been edited by David92595: Feb 2 2012, 05:01 PM

On further rereading the initial post I believe you are probably right - though I still think it's asking for trouble to name a column that same as the table.

Mike

While perhaps not a great practice, there's absolutely no reason why you can't have a field named the same as the table.

I'm getting an error:
"Incorrect syntax near the keyword 'INNER'."

any idea's?

Interesting. That was copied-and-pasted from a working example in Access. Unfortunately, I don't have SQL Server available to test what's different in T-SQL.

Afraid someone with SQL Server will have to find an alternative.

Sorry about that! Good luck with your project.

Try it this way:

UPDATE Loan_Info_Local
SET Judicial_Comments = Judicial_Comments.Judicial_Comments
FROM Loan_Info_Local
INNER JOIN Judicial_Comments ON Loan_Info_Local.TS = Judicial_Comments.TS

Doug thank you for your attempt.

niesz, it worked.

I'd like to thank everyone for helping me on this. In the words of my boss....Sweet

David92595

This post has been edited by David92595: Feb 2 2012, 05:33 PM

Glad we could all assist. (IMG:style_emoticons/default/thumbup.gif)