Have Insert statement, need equivalent Update.

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.Because your procedure don't use sp_executeSql you can use a table variable,
not a temp table.
Declare @.Tab table
(
Field1 nvarchar(10),
Field2 int,
...(exactly the fields in the xml file)
)
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, @.Tab.ID, @.TabParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
@.Tab.scanattribute, getdate(), getdate()
FROM tblScan
INNER JOIN tblScanAttribute
JOIN@.Tab ON tblScanAttribute.Name =
@.Tab.Name
Of course fields must match...
Hope it helps
Benga.
"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...
> 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.
>|||While this is good to know my real problem is that I need the statement
that will do what my insert does accept I need it to be an update
statement. I need the update because an insert is only going to happen
once for each client.
Benga wrote:
> Because your procedure don't use sp_executeSql you can use a table variabl
e,
> not a temp table.
> Declare @.Tab table
> (
> Field1 nvarchar(10),
> Field2 int,
> ...(exactly the fields in the xml file)
> )
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, @.Tab.ID, @.TabParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> @.Tab.scanattribute, getdate(), getdate()
> FROM tblScan
> INNER JOIN tblScanAttribute
> JOIN@.Tab ON tblScanAttribute.Name =
> @.Tab.Name
> Of course fields must match...
> Hope it helps
> Benga.
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...|||Fixed it, no problems.
rhaazy wrote:
> While this is good to know my real problem is that I need the statement
> that will do what my insert does accept I need it to be an update
> statement. I need the update because an insert is only going to happen
> once for each client.
> Benga wrote: