Have Insert statement, need equivalent Update.

[rhaazy]

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for

each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or

if I'm just missing something. Anyway the scan results is stored as an

XML document(@iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.

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

Now here is the insert statement for the table I am having trouble
with.

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

If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.

[Erland Sommarskog]

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

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

Now here is the insert statement for the table I am having trouble
with.

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

If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.

I have some difficulties to understand what your problem is. If all
you want to do is to insert from the XML document, then you don't
need the temp table, but you could use OPENXML directly in the
query.

But then you talk about an UPDATE as well, and if your aim is to insert
new rows, and update existing, it's probably better to use a temp
table (or a table variable), so that you don't have to run OPENXML twice.
Some DB engines support a MERGE command which performs the task of
UPDATE and INSERT in one statement, but this is not available in
SQL Server, not even in SQL 2005.

If this did not answer your question, could you please clarify?

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

My app runs on all my companies PCs every month a scan is performed and
the resulst are stored in a database. So the first time a scan is
performed for any PC it will be an insert, but after that it will
always be an update. I tried using openxml in my insert statement but
kept getting an error stating my sub query is returning more than one
result... So since I couldn't do it that way I'm trying this method.
All the relevent openxml is there I just couldn't figure out how to
insert each column using it. If you have any suggestions I'm open to
give it a try.

Erland Sommarskog wrote:

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

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

Now here is the insert statement for the table I am having trouble
with.

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

If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.

I have some difficulties to understand what your problem is. If all
you want to do is to insert from the XML document, then you don't
need the temp table, but you could use OPENXML directly in the
query.

But then you talk about an UPDATE as well, and if your aim is to insert
new rows, and update existing, it's probably better to use a temp
table (or a table variable), so that you don't have to run OPENXML twice.
Some DB engines support a MERGE command which performs the task of
UPDATE and INSERT in one statement, but this is not available in
SQL Server, not even in SQL 2005.

If this did not answer your question, could you please clarify?

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

Fixed it no problems.
rhaazy wrote:

My app runs on all my companies PCs every month a scan is performed and
the resulst are stored in a database. So the first time a scan is
performed for any PC it will be an insert, but after that it will
always be an update. I tried using openxml in my insert statement but
kept getting an error stating my sub query is returning more than one
result... So since I couldn't do it that way I'm trying this method.
All the relevent openxml is there I just couldn't figure out how to
insert each column using it. If you have any suggestions I'm open to
give it a try.

Erland Sommarskog wrote:

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

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

Now here is the insert statement for the table I am having trouble
with.

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

If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.

I have some difficulties to understand what your problem is. If all
you want to do is to insert from the XML document, then you don't
need the temp table, but you could use OPENXML directly in the
query.

But then you talk about an UPDATE as well, and if your aim is to insert
new rows, and update existing, it's probably better to use a temp
table (or a table variable), so that you don't have to run OPENXML twice.
Some DB engines support a MERGE command which performs the task of
UPDATE and INSERT in one statement, but this is not available in
SQL Server, not even in SQL 2005.

If this did not answer your question, could you please clarify?

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