SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 the multi-part identifier could not be bound
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wilk_u
Starting Member

4 Posts

Posted - 01/27/2013 :  12:40:39  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/27/2013 :  14:23:27  Show Profile  Reply with Quote
do you've the column "code" in temporary table?

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

Go to Top of Page

wilk_u
Starting Member

4 Posts

Posted - 01/28/2013 :  01:58:33  Show Profile  Reply with Quote
Yes, I have
CREATE TABLE [dbo].#tempTable ([Code] VARCHAR(10),
[Description] VARCHAR(30));

Edited by - wilk_u on 01/28/2013 02:12:38
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/28/2013 :  03:35:25  Show Profile  Reply with Quote
are they in the same batch?

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

Go to Top of Page

wilk_u
Starting Member

4 Posts

Posted - 01/28/2013 :  09:42:53  Show Profile  Reply with Quote
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

Edited by - wilk_u on 01/28/2013 09:48:26
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/28/2013 :  10:01:21  Show Profile  Reply with Quote
is begin tran etc also inside same sp?

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

Go to Top of Page

wilk_u
Starting Member

4 Posts

Posted - 01/28/2013 :  11:31:39  Show Profile  Reply with Quote
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!

Edited by - wilk_u on 01/28/2013 11:33:09
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000