the multi-part identifier could not be bound

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

the multi-part identifier could not be bound

Author

Topic

wilk_u
Starting Member

4 Posts

Posted - 2013-01-27 : 12:40:39

Hello,
I have problems with such query:

UPDATE tab SET
tab.[Description] = tmp.[Description]
FROM [dbo].[Table] tab
JOIN [dbo].#tempTable tmp
ON
tab.DomainID = @DomainID AND tab.Code = tmp.Code

AND

INSERT INTO [dbo].[Table](
[DomainID],
[Code],
[Description])
SELECT
@DomainID,
[Code],
[Description] FROM #tempTable tmp
WHERE NOT EXISTS
(SELECT * FROM [dbo].[Table] tab WHERE tab.DomainID = @DomainID AND tab.Code = tmp.Code)

Simply I want to update table and insert new records

In both cases i get error:
The multi-part identifier "tmp.Code" could not be bound.

Have no idea how to solve this.. Any help will be greatly appreciated.
Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-27 : 14:23:27

do you've the column "code" in temporary table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wilk_u
Starting Member

4 Posts

Posted - 2013-01-28 : 01:58:33

Yes, I have
CREATE TABLE [dbo].#tempTable ([Code] VARCHAR(10),
[Description] VARCHAR(30));

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 03:35:25

are they in the same batch?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wilk_u
Starting Member

4 Posts

Posted - 2013-01-28 : 09:42:53

SP
creates Temptable as above and fills it with data then
Begin tran
update as in first post
if error - log error to some table & rollback
else commit

Begin tran
insert as in first post
if error - log error to some table & rollback
else commit

thats the whole code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 10:01:21

is begin tran etc also inside same sp?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wilk_u
Starting Member

4 Posts

Posted - 2013-01-28 : 11:31:39

yes, it's inside the same sp, the sp outline is as described above

i found the issue, without begin tran and rollback and commit clauses it works fine.

How to be able to use one temp table and two transactions in one sp? because for example i want to log error to custom table with field insert when inserting and i want to log error filling field update when updating. simply i want to save logic as i described in previous posts and be able to use my tempTable created at the begging of sp.

Any ideas?
Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources