Update statement, then insert what wasn't available to be updated.

[rhaazy]

Using MS SQL 2000

I have a stored procedure that processes an XML file generated from an

Audit program. The XML looks somewhat like this:
<ComputerScan>
<scanheader>
<ScanDate>somedate&time</ScanDate>
<UniqueID>MAC address</UniqueID>
</scanheader>
<computer>
<ComputerName>RyanPC</ComputerName>
</computer>
<scans>
<scan ID = "1.0" Section= "Basic Overview">
<scanattributes>
<scanattribute ID="1.0.0.0" ParentID=""
Name="NetworkDomian">MSHOMe</scanattribute>
scanattribute ID = "1.0.0.0.0" ParentID="1.0.0.0", etc etc....
This is the Update portion of the sproc....
CREATE PROCEDURE csTest.StoredProcedure1 (@doc ntext)
AS
DECLARE @iTree int
DECLARE @assetid int
DECLARE @scanid int
DECLARE @MAC nvarchar(50)
CREATE TABLE #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
/* SET NOCOUNT ON */
EXEC sp_xml_preparedocument @iTree OUTPUT, @doc
INSERT INTO #temp
SELECT * FROM openxml(@iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@ID',
ParentID nvarchar(50) './@ParentID',
Name nvarchar(50) './@Name',
scanattribute nvarchar(50) '.'
)
SET @MAC = (select UniqueID from openxml(@iTree, 'ComputerScan',
1)with(UniqueID nvarchar(30) 'scanheader/UniqueID'))
IF EXISTS(select MAC from tblAsset where MAC = @MAC)
BEGIN
UPDATE tblAsset set DatelastScanned = (select ScanDate from
openxml(@iTree, 'ComputerScan', 1)with(ScanDate smalldatetime
'scanheader/ScanDate')),
LastModified = getdate() where MAC =
@MAC
UPDATE tblScan set ScanDate = (select ScanDate from
openxml(@iTree,
'ComputerScan', 1)with(ScanDate smalldatetime 'scanheader/ScanDate')),
LastModified = getdate() where MAC =
@MAC
UPDATE tblScanDetail set GUIID = #temp.ID, GUIParentID =
#temp.ParentID, AttributeValue = #temp.scanattribute, LastModified =
getdate()
FROM tblScanDetail INNER JOIN #temp
ON (tblScanDetail.GUIID = #temp.ID AND
tblScanDetail.GUIParentID =
#temp.ParentID AND tblScanDetail.AttributeValue = #temp.scanattribute)
WHERE MAC = @MAC
!!!!!!!!!!!!!!!!!! THIS IS WHERE IT SCREWS UP, THIS NEXT INSERT
STATEMENT IS SUPPOSE TO HANDLE attributes THAT WERE NOT IN THE PREVIOUS

SCAN SO CAN NOT BE UDPATED BECAUSE THEY DON'T EXIST
YET!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
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)
END
ELSE
BEGIN
Here are a few table defintions to maybe help out a little too...
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =

1)
drop table [dbo].[tblAsset]
GO
CREATE TABLE [dbo].[tblAsset] (
[AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,

[AssetName] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
,
[AssetTypeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DatelastScanned] [smalldatetime] NULL ,
[NextScanDate] [smalldatetime] NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
-----------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblScan]
GO
CREATE TABLE [dbo].[tblScan] (
[ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[AssetID] [int] NULL ,
[ScanDate] [smalldatetime] NULL ,
[AssetName] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NOT NULL
) ON [PRIMARY]
GO
----------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblScanDetail]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblScanDetail]
GO
CREATE TABLE [dbo].[tblScanDetail] (
[ScanDetailID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT
NULL ,
[ScanID] [int] NULL ,
[ScanAttributeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[GUIID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
,
[GUIParentID] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[AttributeValue] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NOT NULL
) ON [PRIMARY]
GO
------------------------------------------------------------
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.

[Erland Sommarskog]

rhaazy (rh****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[rhaazy]

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblScanDetail]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblScanDetail]
GO

CREATE TABLE [dbo].[tblScanDetail] (
[ScanDetailID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ScanID] [int] NULL ,
[ScanAttributeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GUIID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[GUIParentID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AttributeValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NOT NULL
) ON [PRIMARY]
GO

------------------------------------

ALTER PROCEDURE csTest.StoredProcedure1 (@doc ntext)

AS
DECLARE @iTree int
DECLARE @assetid int
DECLARE @scanid int
DECLARE @MAC nvarchar(50)
CREATE TABLE #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))

/* SET NOCOUNT ON */

EXEC sp_xml_preparedocument @iTree OUTPUT, @doc

INSERT INTO #temp
SELECT * FROM openxml(@iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@ID',
ParentID nvarchar(50) './@ParentID',
Name nvarchar(50) './@Name',
scanattribute nvarchar(50) '.'
)

SET @MAC = (select UniqueID from openxml(@iTree, 'ComputerScan',
1)with(UniqueID nvarchar(30) 'scanheader/UniqueID'))

IF EXISTS(select MAC from tblAsset where MAC = @MAC)
BEGIN
UPDATE tblAsset set DatelastScanned = (select ScanDate from
openxml(@iTree, 'ComputerScan', 1)with(ScanDate smalldatetime
'scanheader/ScanDate')),
LastModified = getdate() where MAC = @MAC

UPDATE tblScan set ScanDate = (select ScanDate from openxml(@iTree,
'ComputerScan', 1)with(ScanDate smalldatetime 'scanheader/ScanDate')),
LastModified = getdate() where MAC = @MAC

UPDATE tblScanDetail set GUIID = #temp.ID, GUIParentID =
#temp.ParentID, AttributeValue = #temp.scanattribute, LastModified =
getdate()
FROM tblScanDetail INNER JOIN #temp
ON (tblScanDetail.GUIID = #temp.ID AND tblScanDetail.GUIParentID =
#temp.ParentID AND tblScanDetail.AttributeValue = #temp.scanattribute)
WHERE MAC = @MAC

/*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)*/
END
ELSE
BEGIN
INSERT INTO tblAsset (AssetName, MAC, DatelastScanned, DateCreated,
LastModified)
SELECT *, DateCreated = getdate(), LastModified = getdate() FROM
openxml(@iTree, 'ComputerScan', 1)
WITH (
ComputerName nvarchar(30) 'computer/ComputerName',
MAC nvarchar(30) 'scanheader/UniqueID',
DatelastScanned smalldatetime 'scanheader/ScanDate'
)

SET @assetid = scope_identity()

INSERT INTO tblScan ( AssetID, AssetName, ScanDate, MAC, DateCreated,
LastModified)
SELECT @assetid, *, LastModified = getdate(), DateCreated =
getdate() FROM openxml(@iTree, 'ComputerScan', 1)
WITH (
ComputerName nvarchar(30) 'computer/ComputerName',
ScanDate smalldatetime 'scanheader/ScanDate',
MAC nvarchar(30) 'scanheader/UniqueID'
)

SET @scanid = scope_identity()

INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, @scanid,
#temp.scanattribute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp
ON tblScanAttribute.Name = #temp.Name
END
DROP TABLE #temp

EXEC sp_xml_removedocument @iTree

RETURN
---------------------------------------
that is the entire stored proc
-----------------------------------------
The sproc gets the Unique ID for each PC (the MAC address) and compares
that with the MAC addresses in tblAsset. If the MAC exists Update, if
not Insert... AssetID is the PK and is an Identity column. tblScan
has ScanID as PK and Identity column.
tblScanAttributes PK is ScanAttributeID, and tblScanDetail has an
identity column ScanDetailID as the PK. Here are sampels of all the
tables...
-------------------------------------
tblAsset
AssetID AssetName MAC
1 RyanPC xx:xx:xx:xx:xx:xx
-------------------------------------
tblScan
ScanID AssetID MAC LastScanned
1 1 xx:xx:xx:xx:xx:xx dd/mm/yy
------------------------------------
tblScanAttribute
ScanAttributeID ScanSection Name
1 Basic OverView Asset Tag
2 Basic OverView BIOS Vers.
3 Basic OverView ComputerName
4 Basic OverView Manufacturer
.....
18 Drives Bytes Per Cluster
18 Drives Drive Type
18 Drives File System Type
......
31 Error Log Log FIle Name
31 Error Log Message
etc etc.
--------------------------------------
tblScanDetail
ID ScanID ScanAttributeID MAC GUID GUIParentID Value
1 1 3 xx:xx 1.0.0 RyanPC
2 1 7 xx:xx 1.0.1 MSHOME
3 1 4 xx:xx 1.0.2 Server
.......
18 1 23 xx:xx 2.0.0 A
19 1 19 xx:xx 2.0.0.0 2.0.0 RemovableDisk
20 1 23 xx:xx 2.0.1 C
21 1 19 xx:xx 2.0.1.0 2.0.1 LocalDisk
22 1 25 xx:xx 2.0.1.1 2.0.1 93%
etc etc.....

My problem as stated earlier is that after each assets FIRST scan,
there information will already be there. When any Scan after the first
is received by the server, it will call the UPDATE portion of my sproc.
After updating tblScanDetail, there may be information left from the
XML that wasn't updated due obviously to the nature of the source. So
the Insert has to take place. However I can't seem to get the logic
of Inserting what wasn't just updated....

[rhaazy]

Also as part of my previous post here is the Insert statement I have so
far that isn't doing what it is suppose to do, which is INSERT INTO
tblScanDetail (everything that wasn't updated 'if an update occured')

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)

[Alexander Kuznetsov]

What you are asking for is precisely what MERGE statement does in DB2
and Oracle.
On SQL Server 2005, use OUTPUT clause, as described in:

http://sql-server-tips.blogspot.com/...nt-in-sql.html

set nocount on
go
drop table permanent
drop table staging
go
create table permanent(id int, d float, comment varchar(15))
go
insert into permanent values(1, 10., 'Original Row')
insert into permanent values(2, 10., 'Original Row')
insert into permanent values(3, 10., 'Original Row')
go
create table staging(id int, d float)
go
insert into staging values(2, 15.)
insert into staging values(3, 15.)
insert into staging values(4, 15.)
go
select * from permanent

id d comment
----------- ---------------------- ---------------
1 10 Original Row
2 10 Original Row
3 10 Original Row

go
declare @updated_ids table(id int)
update permanent set d=s.d, comment = 'Modified Row'
output inserted.id into @updated_ids
from permanent p, staging s
where p.id=s.id

insert into permanent
select id, d, 'New Row' from staging where id not in(select id from
@updated_ids)
go
select * from permanent
go

id d comment
----------- ---------------------- ---------------
1 10 Original Row
2 15 Modified Row
3 15 Modified Row
4 15 New Row

[rhaazy]

Yes, I want to mimick the Merge Statement! Yay!

Thanks for clearing that up for me, the example you have and the page
were helpful however I still can't get the d*** thing to work right...

[rhaazy]

rhaazy wrote:

Yes, I want to mimick the Merge Statement! Yay!

Thanks for clearing that up for me, the example you have and the page
were helpful however I still can't get the d*** thing to work right...

UPDATE tblScanDetail set GUIID = #temp.ID, GUIParentID =
#temp.ParentID, AttributeValue = #temp.scanattribute, LastModified =
getdate()
OUTPUT inserted.GUIID into @updated
FROM tblScanDetail INNER JOIN #temp
ON (tblScanDetail.GUIID = #temp.ID AND
tblScanDetail.GUIParentID =
#temp.ParentID AND tblScanDetail.AttributeValue = #temp.scanattribute)
WHERE MAC = @MAC

INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, @scanid,
#temp.scanattribute, DateCreated = getdate(),
LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp
ON tblScanAttribute.Name = #temp.Name
WHERE GUIID not in (select GUIID from @updated)

I get an error when I do this, but I believe this to be the correct
method whats wrong?

[rhaazy]

ah ahaha all I needed to do was this...
use my normal insert statement and add a where clause to the end of it
like this..

UpdateTblScanDetail....

Insert into TblScanDetail
where #temp.ID not in (select GUIID from tblScanDetail)

doh! way too simple....

[Erland Sommarskog]

rhaazy (rh****@gmail.com) writes:

ah ahaha all I needed to do was this...
use my normal insert statement and add a where clause to the end of it
like this..

UpdateTblScanDetail....

Insert into TblScanDetail
where #temp.ID not in (select GUIID from tblScanDetail)

doh! way too simple....

So, you issue is resolved then, and I don't have to scrutinize your
earlier posts.

Just a comment. I prefer to write the condition as:

NOT EXISTS (SELECT *
FROM tblScanDetail SD
WHERE SD.GUIID = #temp.ID)

There are two problems with NOT IN, that NOT EXISTS does not have:

1) you can get lost when there are NULL values involved.
2) works only with one-column conditions.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[rhaazy]

There are two problems with NOT IN, that NOT EXISTS does not have:

1) you can get lost when there are NULL values involved.
2) works only with one-column conditions.

Well luckily for me it is unacceptible for GUIID to be null and that is
the only column I need to have a condition for.