| Author |
 Topic  |
|
|
wilk_u
Starting Member
4 Posts |
 Posted - 01/27/2013 : 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
India
47023 Posts |
Posted - 01/27/2013 : 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 - 01/28/2013 : 01:58:33
|
Yes, I have
CREATE TABLE [dbo].#tempTable ([Code] VARCHAR(10),
[Description] VARCHAR(30)); |
Edited by - wilk_u on 01/28/2013 02:12:38 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/28/2013 : 03:35:25
|
are they in the same batch?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
wilk_u
Starting Member
4 Posts |
Posted - 01/28/2013 : 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 |
Edited by - wilk_u on 01/28/2013 09:48:26 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/28/2013 : 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 - 01/28/2013 : 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! |
Edited by - wilk_u on 01/28/2013 11:33:09 |
|
|
| |
Topic |
|
the multi-part identifier could not be bound
