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,
tblScanAttribut e.ScanAttribute ID, tblScan.ScanID,
#temp.scanattri bute, DateCreated = getdate(),
LastModified =
getdate()
FROM tblScan, tblScanAttribut e JOIN #temp ON
tblScanAttribut e.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]

Re: Have Insert statement, need equivalent Update.

rhaazy (rhaazy@gmail.c om) writes:[color=blue]
> 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,
> tblScanAttribut e.ScanAttribute ID, tblScan.ScanID,
> #temp.scanattri bute, DateCreated = getdate(),
> LastModified =
> getdate()
> FROM tblScan, tblScanAttribut e JOIN #temp ON
> tblScanAttribut e.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.[/color]

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, 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: Have Insert statement, need equivalent Update.

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:[color=blue]
> rhaazy (rhaazy@gmail.c om) writes:[color=green]
> > 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,
> > tblScanAttribut e.ScanAttribute ID, tblScan.ScanID,
> > #temp.scanattri bute, DateCreated = getdate(),
> > LastModified =
> > getdate()
> > FROM tblScan, tblScanAttribut e JOIN #temp ON
> > tblScanAttribut e.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.[/color]
>
> 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, esquel@sommarsk og.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[/color]

[rhaazy]

Re: Have Insert statement, need equivalent Update.

Fixed it no problems.
rhaazy wrote:[color=blue]
> 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:[color=green]
> > rhaazy (rhaazy@gmail.c om) writes:[color=darkred]
> > > 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,
> > > tblScanAttribut e.ScanAttribute ID, tblScan.ScanID,
> > > #temp.scanattri bute, DateCreated = getdate(),
> > > LastModified =
> > > getdate()
> > > FROM tblScan, tblScanAttribut e JOIN #temp ON
> > > tblScanAttribut e.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.[/color]
> >
> > 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, esquel@sommarsk og.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[/color][/color]