Is this query possible?

Question

Is this query possible?

RynoB

SSC Eights!

Points: 802
More actions
May 21, 2008 at 2:25 am

#186792

Hi could someone please point out if this solution is possible or perhaps if I should consider an alternative solution to what I'm trying to do.
Using OPENXML my constructed xml should either update existing records in a table or alternatively insert a new record if it does not yet exist
This is my code:
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString
-- do an update on existing record
UPDATE Table1
SET Amount = alloc.amount
FROM OPENXML (@idoc, '/r/a',2)
WITH
(
id INT
,col1 INT
,col2 INT
,col3 DECIMAL
) aa
INNER JOIN Table1 t ON ca.BudgetCentreID = aa.bid AND t.projectid = aa.pid AND t.AccountID = aa.accid
EXEC sp_xml_removedocument @idoc
-- see which record does not yet exist then do an insert
INSERT INTO uAllocation
SELECT bb.id, bb.col1, bb.col2, bb.col3
FROM
(
SELECT id, col1, col2, col3
FROM aa
WHERE id NOT IN (SELECT id FROM aa)
)bb
This however gives me the following error:
Invalid object name 'aa'
Clearly I'm doing something wrong here but cannot spot it after several hours of thinking and looking.
Your help is much appreciated.
Thanks.
R

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

As far as I can see, aa is just an alias for the openXML in the first query. It won't last longer than the execution of the first query. Hence, in the second

SELECT bb.id, bb.col1, bb.col2, bb.col3

FROM

(

SELECT id, col1, col2, col3

FROM aa

WHERE id NOT IN (SELECT id FROM aa)

)bb

There's no table or view named aa for it to reference.

I would suggest you insert the results of OPENXml into a temp table, then use the temp table in the insert and update.

So, something like this (pseudocode follows)

Insert into #SomeTempTable

SELECT <Stuff> FROM OPENXML(<Details> )

Udate TABLE1 set <Stuff>

FROM #SomeTable

WHERE <Stuff>

INSERT INTO Table1

SELECT <Stuff> FROM #SomeTempTable WHERE <Stuff>

Does that make any sense?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RynoB SSC Eights! Points: 802 More actions · Answer 2

Gail, thank you, what you said made sense and I reworked the query accordingly.

Thanks for your help.