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>somed ate&time</ScanDate>
<UniqueID>MAC address</UniqueID>
</scanheader>
<computer>
<ComputerName>R yanPC</ComputerName>
</computer>
<scans>
<scan ID = "1.0" Section= "Basic Overview">
<scanattributes >
<scanattribut e ID="1.0.0.0" ParentID=""
Name="NetworkDo mian">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.StoredPr ocedure1 (@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_prepared ocument @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.scanattri bute, LastModified =
getdate()
FROM tblScanDetail INNER JOIN #temp
ON (tblScanDetail. GUIID = #temp.ID AND
tblScanDetail.G UIParentID =
#temp.ParentID AND tblScanDetail.A ttributeValue = #temp.scanattri bute)
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,
tblScanAttribut e.ScanAttribute ID,
@scanid, b.scanattribute , DateCreated = getdate(), LastModified =
getdate()
FROM tblScanDetail LEFT OUTER JOIN #temp a ON
(tblScanDetail. GUIID =
a.ID AND tblScanDetail.G UIParentID = a.ParentID AND
tblScanDetail.A ttributeValue = a.scanattribute ), tblScanAttribut e JOIN
#temp b ON tblScanAttribut e.Name = b.Name
WHERE (tblScanDetail. GUIID IS NULL AND
tblScanDetail.G UIParentID IS
NULL AND tblScanDetail.A ttributeValue 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_tblA sset]') and OBJECTPROPERTY( id,
N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblA sset
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_Gene ral_CP1_CI_AS NULL
,
[AssetTypeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_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_Gene ral_CP1_CI_AS NULL
,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_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_Gene ral_CP1_CI_AS NULL
,
[GUIID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL
,
[GUIParentID] [nvarchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[AttributeValue] [nvarchar] (50) COLLATE
SQL_Latin1_Gene ral_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]

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

rhaazy (rhaazy@gmail.c om) writes:[color=blue]
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID,
> ScanAttributeID ,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @MAC, b.ID, b.ParentID,
> tblScanAttribut e.ScanAttribute ID,
> @scanid, b.scanattribute , DateCreated = getdate(), LastModified =
> getdate()
> FROM tblScanDetail LEFT OUTER JOIN #temp a ON
> (tblScanDetail. GUIID =
> a.ID AND tblScanDetail.G UIParentID = a.ParentID AND
> tblScanDetail.A ttributeValue = a.scanattribute ), tblScanAttribut e JOIN
> #temp b ON tblScanAttribut e.Name = b.Name
> WHERE (tblScanDetail. GUIID IS NULL AND
> tblScanDetail.G UIParentID IS
> NULL AND tblScanDetail.A ttributeValue 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.[/color]

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 tblScanAttribut e. 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 tblScanAttribut e
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@sommarsk og.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

[rhaazy]

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

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_Gene ral_CP1_CI_AS NULL ,
[GUIID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL
,
[GUIParentID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[AttributeValue] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NOT NULL
) ON [PRIMARY]
GO

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

ALTER PROCEDURE csTest.StoredPr ocedure1 (@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_prepared ocument @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.scanattri bute, LastModified =
getdate()
FROM tblScanDetail INNER JOIN #temp
ON (tblScanDetail. GUIID = #temp.ID AND tblScanDetail.G UIParentID =
#temp.ParentID AND tblScanDetail.A ttributeValue = #temp.scanattri bute)
WHERE MAC = @MAC

/*INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID ,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @MAC, b.ID, b.ParentID, tblScanAttribut e.ScanAttribute ID,
@scanid, b.scanattribute , DateCreated = getdate(), LastModified =
getdate()
FROM tblScanDetail LEFT OUTER JOIN #temp a ON (tblScanDetail. GUIID =
a.ID AND tblScanDetail.G UIParentID = a.ParentID AND
tblScanDetail.A ttributeValue = a.scanattribute ), tblScanAttribut e JOIN
#temp b ON tblScanAttribut e.Name = b.Name
WHERE (tblScanDetail. GUIID IS NULL AND tblScanDetail.G UIParentID IS
NULL AND tblScanDetail.A ttributeValue 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,
tblScanAttribut e.ScanAttribute ID, @scanid,
#temp.scanattri bute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScan, tblScanAttribut e JOIN #temp
ON tblScanAttribut e.Name = #temp.Name
END


DROP TABLE #temp

EXEC sp_xml_removedo cument @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.
tblScanAttribut es 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
------------------------------------
tblScanAttribut e
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]

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

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,
tblScanAttribut e.ScanAttribute ID,
@scanid, b.scanattribute , DateCreated = getdate(), LastModified =
getdate()
FROM tblScanDetail LEFT OUTER JOIN #temp a ON
(tblScanDetail. GUIID =
a.ID AND tblScanDetail.G UIParentID = a.ParentID AND
tblScanDetail.A ttributeValue = a.scanattribute ), tblScanAttribut e JOIN
#temp b ON tblScanAttribut e.Name = b.Name
WHERE (tblScanDetail. GUIID IS NULL AND
tblScanDetail.G UIParentID IS
NULL AND tblScanDetail.A ttributeValue IS NULL)

[Alexander Kuznetsov]

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

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/2006/06/mimicking-merge-statement-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]

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

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]

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


rhaazy wrote:[color=blue]
> 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...[/color]

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



INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID ,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @MAC, #temp.ID, #temp.ParentID,
tblScanAttribut e.ScanAttribute ID, @scanid,
#temp.scanattri bute, DateCreated = getdate(),
LastModified =
getdate()
FROM tblScan, tblScanAttribut e JOIN #temp
ON tblScanAttribut e.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]

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

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..

UpdateTblScanDe tail....

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

doh! way too simple....

[Erland Sommarskog]

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

rhaazy (rhaazy@gmail.c om) writes:[color=blue]
> 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..
>
> UpdateTblScanDe tail....
>
> Insert into TblScanDetail
> where #temp.ID not in (select GUIID from tblScanDetail)
>
> doh! way too simple....[/color]

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, esquel@sommarsk og.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

[rhaazy]

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

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.