|
|
Posted by Erland Sommarskog on 06/29/06 21:48
rhaazy (rhaazy@gmail.com) writes:
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID,
> ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @MAC, b.ID, b.ParentID,
> tblScanAttribute.ScanAttributeID,
> @scanid, b.scanattribute, DateCreated = getdate(), LastModified =
> getdate()
> FROM tblScanDetail LEFT OUTER JOIN #temp a ON
> (tblScanDetail.GUIID =
> a.ID AND tblScanDetail.GUIParentID = a.ParentID AND
> tblScanDetail.AttributeValue = a.scanattribute), tblScanAttribute JOIN
> #temp b ON tblScanAttribute.Name = b.Name
> WHERE (tblScanDetail.GUIID IS NULL AND
> tblScanDetail.GUIParentID IS
> NULL AND tblScanDetail.AttributeValue IS NULL)
>...
> ------------------------------------------------------------
> My problem is that Insert statement that follows the update into
> tblScanDetail, for some reason it just seems to insert everything twice
> if the update is performed. Not sure what I did wrong but any help
> would be appreciated. Thanks in advance.
Since you did not seem to post the complete XML document it was a
difficult to test. And while you did post the table schemas, you did
not explain the tables, and there were no keys. And you did not include
the definition of tblScanAttibute.
But just like last night I notice that your query includes a cross
join with tblScanAttribute. Your reply was that I hit the nail on
the head, so I'm a little puzzled why you post a similar query tonight...
What also appears funny is that judging from your talk about UPDATE
and INSERT, I would expect an INSERT ... SELECT .. FROM #temp WHERE
NOT EXISTS, but your query is completely different.
Anyway, a complete sample document, the definition of tblScanAttribute
and INSERT statemetns to that table, and finally the expected result.
I think you can skip the UPDATE - at least if you get the problems
with an empty table as well.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|