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. 3 1809
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Sans Spam |
last post by:
Greetings! I have a table that contains all of the function
permissions within a given application. These functions are different
sections of a site and each has its own permissions (READ, WRITE,...
|
by: Mike Chirico |
last post by:
Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004
The latest...
|
by: Karen Middleton |
last post by:
In MS Access I can do in one SQL statement a update if exists else a
insert.
Assuming my source staging table is called - SOURCE and my target
table is called - DEST and both of them have the...
|
by: serge |
last post by:
/*
This is a long post. You can paste the whole message
in the SQL Query Analyzer.
I have a scenario where there are records
with values pointing to wrong records and I need to fix them
using an...
|
by: pk |
last post by:
Sorry for the piece-by-piece nature of this post, I moved it from a
dormant group to this one and it was 3 separate posts in the other
group. Anyway...
I'm trying to bulk insert a text file of...
|
by: Alex |
last post by:
Hi,
I need to create a trigger that will trap the insert commands on a
table and if the row already exists, it updates the information.
I started with this exemple but im getting syntax...
|
by: William |
last post by:
i am using a dataset and a DataAdapter to update a table with the
following schema:
ResourceID
ProjectID
LastName
FirstName
Year
Nov
Dec
Jan
|
by: rhaazy |
last post by:
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>...
|
by: Mukesh |
last post by:
sir,
i am developing a database, which will store the users profile
both personal and professional
which includes the address, telephone, gender and etc.
in my main table i have created a column...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| | |