|
Assumption: you mean that you want to select the rows which have the lowest date, whichever table they come from.
On this assumption, I think doing a UNION of the tables is probably about right.
SELECT [ID], MIN( Dateentered ) AS MinDate
FROM Table1 UNION ALL Table2
GROUP BY [ID] If you actually want to insert rows or update rows in table 2, that's a bit trickier, and would have to be done in two stages:
-- Insert any rows into Table2 that are in Table1
-- but not in Table2
INSERT INTO Table2
SELECT T1.[ID], T1.Dateentered
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.[ID] = T2.[ID]
WHERE T2.[ID] IS NULL
-- Set Table2's entries to the date in Table1,
-- if table1's are earlier
UPDATE Table2
SET Dateentered = T1.Dateentered
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.[ID] = T2.[ID]
WHERE
[ID] = T1.[ID] AND
T1.Dateentered < T2.Dateentered
|
|
|
|
|
Hello,
I wonder if anyone can help me with this.
I try to insert a record in a DataTable, and everything works absolutely perfect. UNTIL I create a custom DataView object based on the table.
Here is a small sample, when you take the DataView myView = new DataView(table); line out of comment the record isn't at the correct position anymore.
Any help is much appreciated !!!!
DataTable table = new DataTable("Table");<br />
<br />
DataColumn colID = table.Columns.Add("ID", typeof(int));<br />
DataColumn colName = table.Columns.Add("Name", typeof(String));<br />
<br />
colID.AllowDBNull = false;<br />
<br />
table.Rows.Add(new object[] {1, "Row 1"});<br />
table.Rows.Add(new object[] {2, "Row 2"});<br />
table.Rows.Add(new object[] {4, "Row 4"});<br />
<br />
table.AcceptChanges();<br />
<br />
<br />
foreach(DataRowView row in table.DefaultView)<br />
{<br />
Console.WriteLine(row["Name"]);<br />
}<br />
<br />
Console.WriteLine("-------------------------------------------------------");<br />
<br />
DataRowView cindyView = table.DefaultView.AddNew();<br />
cindyView["ID"] = 3;<br />
cindyView["Name"] = "Row 3";<br />
<br />
Object[] ItemArray = cindyView.Row.ItemArray;<br />
cindyView.CancelEdit();<br />
<br />
DataRow cindy = table.NewRow();<br />
cindy.ItemArray = ItemArray;<br />
cindy.EndEdit();<br />
table.Rows.InsertAt(cindy, 2);<br />
<br />
table.DefaultView.RowStateFilter = DataViewRowState.None;<br />
table.DefaultView.RowStateFilter = DataViewRowState.CurrentRows;<br />
<br />
foreach(DataRowView row in table.DefaultView)<br />
{<br />
Console.WriteLine(row["Name"]);<br />
}
|
|
|
|
|
It would help if you posted the actual output from this code and what you expected to see.
IIRC, unless you've applied a primary key constraint, or some kind of sort criteria, your view will show the records in the order they were inserted.
|
|
|
|
|
I get the following output if I don't create the additional DataView:
Row 1
Row 2
Row 4
-----
Row 1
Row 2
Row 3
Row 4
And this is the output when the DataView object is created based on the table:
Row 1
Row 2
Row 4
-----
Row 1
Row 2
Row 4
Row 3
I don't see, why the DevaultView of the table changes the order of the DataViewRows, just because I create another DataView object based on this DataTable. Also, when I go in debug mode and check the Rows collection of the DataTable, the rows appear as one would expect (1, 2, 3, 4).
|
|
|
|
|
Hi
I have a datagrid bound to a dataset. I can insert and update thru the grid but a delete returns the following message (through OleDbException.Message)
"Incorrect syntax near the keyword DEFAULT"
This is thown when I call the OleDbDataAdapter.Update() method using my dataset as the target. The following is my code for the delete command of the dataAdapter:
<br />
string sql = "DELETE FROM Product " +<br />
"WHERE productID = ? AND desription = ? AND " +<br />
"cost = ? AND srp = ?";<br />
<br />
OleDbCommand cmd = new OleDbCommand(sql, cn);<br />
<br />
OleDbParameterCollection pc = cmd.Parameters;<br />
OleDbParameter param;<br />
param = pc.Add("ProductID", OleDbType.Integer, 0, "prouctID");<br />
param.SourceVersion = DataRowVersion.Original;<br />
param = pc.Add("Description", OleDbType.VarChar, 50, "description");<br />
param.SourceVersion = DataRowVersion.Original;<br />
param = pc.Add("Cost", OleDbType.Currency, 0, "cost");<br />
param.SourceVersion = DataRowVersion.Original;<br />
param = pc.Add("SRP", OleDbType.Currency, 0, "srp");<br />
param.SourceVersion = DataRowVersion.Original;<br />
|
|
|
|
|
swarm wrote:
param = pc.Add("ProductID", OleDbType.Integer, 0, "prouctID");
Could it be that the last parameter to Add should be 'productID'?
|
|
|
|
|
I'm not sure about that but I thought that the order of the parameters in the parameterCollection should reflect to the order on the SQL statement. I do not find the rationale of adding the product id parameter last. I will try your suggestion though. Thanks
|
|
|
|
|
That wasn't what I was trying to say. The parameters in the collection must be in the same order as in the statement, for OleDbCommand.
I was assuming that you'd copied and pasted your code when making your post. There are a number of typos or, at least, inconsistencies between the statement, the parameter names, and the column names.
Specifically, in the SQL statement you have 'desription' but in the column name and parameter name you have 'description', and also you have 'productID' in SQL, but the column name parameter has 'prouctID'.
|
|
|
|
|
Thank you so much and I'm so sorry for the confusion. I should have perused my codes carefully. There were indeed typos.
Cheers
|
|
|
|
|
|
Dear friends,
I am new to databases and have many questions swinging in my head. The main question is related to the concurrency and consistency issue.
In .NET, Oracle, and Access client server systems, how the DB system manage the different copies of same data to different users. Suppose a client opens a record set to perform some operation on it and write results back in the database. At the same time some other client updates the same rows, then how error is detected and handled by first client. How much control as such do we have and how pure is the object orientation ?
|
|
|
|
|
Try searching the MSDN website for "optimistic locking".
On the system I have just developed, we have a "VersionNo" column. Whenever we retreive a resultset that may be modified by the user, we include this column in the column-list.
When we update the SQL-Server database we use stored-procedures that contain:
UPDATE SomeTable SET
SomeColumn = @SomeValue,
VersionNo = CASE WHEN VersionNo > 32000 THEN 1 ELSE VersionNo + 1 END,
LastUpdateUser = @UserId,
LastUpdateDate = GetDate()
WHERE SomeKeyColumn = @SomeKeyValue
AND VersionNo = @OriginalVersionNo
SELECT @intError = @@ERROR, @intRowCount = @@ROWCOUNT
IF @intRowCount <> 1 BEGIN
RAISERROR('Concurrency problem - someone else has updated record', 16, 1)
RETURN 1
ENDThis "VersionNo" value is incremented each time that we update the database. If the number of rows updated (@intRowCount) is not one then someone else has updated your record after you selected it. The case-statement stops the version-number from exceeding the size of the field (smallint in this case).
You can use similar code for Oracle and MS-Access databases - and you don't need to be using stored procedures (although it means that your concurrency control is isolated in a single place in your code).
Hope this helps.
Andy
|
|
|
|
|
There's also the SQL Server ROWVERSION datatype, which SQL Server automatically updates whenever the row is changed. However, IIRC, it doesn't write a predictable sequence number, so if you need it to show versions 1, 2, 3, etc in sequence, you'll have to do it as above.
|
|
|
|
|
|
I'm having a problem with when (or even if) the DataRowState of DataRows are getting changed. Here's the situation:
I have one main table which is bound to a form which displays one record from the main table. If you make changes to the form and then try and update the database, nothing happens. If you move to another record and then try it, it works. I've determined that this is because even though the DataRow has a Proposed version set (HasVersion(DataRowVersion.Proposed)) returns true, the DataRowState is still Unchanged until you move to another record. Does anybody know why this is and if I can change the DataRowState myself?
This situation is a fairly minor problem by itself, but it becomes a major problem because I also have a child table linked to the main table and that never gets changed to DataRowState.Modified so I can never update it.
Please help!!!
|
|
|
|
|
Can you post the codes for the update?
|
|
|
|
|
I have created a sql script select statement which when run as a script in query analyzer brings back results in less then a second. When the same script is put into a stored procedure and that is executed in query analyzer the server is not able to bring back results in a timely manner. I stop the query after 20 seconds because it kills the server. My guess is that the problem is related to indexing and the stored procedure is compiling on the wrong index. Is there a way to force the stored procedure to compile on the correct index? I have added several ‘with(Index=’???’)’ clauses it has had no affect on the results. Anyone have a suggestion? Thanks
Jason W.
|
|
|
|
|
Can you post the stored procedure here, with details of the indexes that you think should be used and the number of rows expected from each of your tables.
Query Analyzer contains an option to show a graphical "Explain Plan" view of how it is accessing the tables and indexes in your database. If you don't know how to use this then I would heartily suggest that you spend some time become familiar.
From experience, SQL-Server seems prone to using hash-joins to link tables together. Sometimes rewriting your query from:
select * from Department D<br />
inner join Employee E<br />
on E.DeptId = D.DeptId
to:
select * from Department D<br />
inner loop join Employee E<br />
on E.DeptId = D.DeptId
where "loop" is the optimizer hint for a nested-inner join yields better results (if supported by indexes). I also normally order the from clause in the sequence that I would expect the query-optimizer to work (it shouldn't make any difference - but it does serve as useful documentation to other programmers who look at my code).
Tuning queries is a bit of an art-form - there are many different techniques available (clustered indexes, covered indexes, hints, juditious use of temporary tables, inline-views, etc.). I would need to know more details about your specific situation before being able to give a better answer.
Andy
|
|
|
|
|
I just tried the stored procedure using the loop join and it is 100 times faster. I hadn’t heard of that type of join before. Can you recommend a good web site or book where I can get more information on developing databases and stored procedures maximizing performance?
Thanks for your detailed response.
Jason W.
|
|
|
|
|
loop doesn't indicate a different type of join - it causes the underlying query interpreter to use a different logical operator to perform the operation.
It appears that the original query (without the hint) was using a hash-match join: hashing the data selected from one side of the join and comparing to a table of hashes generated from the other side of the join.
Adding the loop hint causes it to use the Nested Loops operator, which is the simplistic way to implement a join: consider each row on the left-hand side in turn, look through the right-hand side to see if anything matches. The loop over the right-hand side is nested inside the loop for the left-hand side; hence, Nested Loops (although SQL Server may decide to swap the tables or indexes over if appropriate).
If there isn't much data on at least one side of the join, nested loops can perform better than hash match. The downside of nested loops is simply that the server must iterate over every row of one side the number of times that there are rows on the other side.
When compiling a stored procedure, SQL Server caches the compiled execution plan. It does this the first time that the procedure is executed. When pressure for memory increases, SQL Server removes any pages that haven't been touched recently from cache - including cached execution plans.
However, when compiling the procedure for the first time, SQL Server is guided by the parameters. If the parameters will be very different, causing greatly different execution paths, the cached execution plan can be terrible. To force a recompile every time, add WITH RECOMPILE before the AS keyword in the definition of the procedure. As always, you should measure the effect this has.
Another reason the query optimizer might pick poor indexes is if your index statistics are not up to date. Generally SQL Server updates statistics automatically, but you might have turned this off. Look up UPDATE STATISTICS and sp_autostats. Alternatively, consider updating statistics via a scheduled maintenance job. The statistics tell SQL Server how selective an index is, and thus how useful it is in finding rows.
If your execution plan indicates that SQL Server is having to join two indexes on one table together - perhaps seeking one and scanning the other - and your query didn't join the table onto itself, you may find that defining a new index that includes all the columns projected or referenced (excluding the primary key columns, if this table has a clustered index) may help. If a single index contains all the data required, the query is referred to as a covered query. SQL Server can handle a covered query simply by reading the index - it never has to read the table, since all data is stored in the index.
I mention excluding the primary key columns if a clustered index. This is because, if a table has a clustered index, SQL Server stores the clustered index data as the 'destination' part of all non-clustered indexes - to find the rest of the row, after reading the non-clustered index, it has to seek through the clustered index. If there isn't a clustered index, it stores the row number instead.
Care should be taken, because inserts, updates and deletes take more time the more indexes are present.
For more information, try http://www.sql-server-performance.com/[^] or Inside SQL Server 2000[^] by Kalen Delaney (MS Press) - also see the book's website[^].
|
|
|
|
|
Thanks for the detailed response. I am still trying to figure out what a hint is, andy’s message made reference to it also, I will look into that. I have taken a quick look through at the http://www.sql-server-performance.com site you recommended it appears to have a great deal of content. Taking a look at a few of the articles there is a lot I need to learn.
Again, I truly appreciate the your help.
Jason W.
|
|
|
|
|
Hint \Hint\, v. i.
To make an indirect reference, suggestion, or allusion; to
allude vaguely to something.(from www.dict.org[^]).
Basically, it's a non-compulsory suggestion to SQL Server's query optimiser about how to perform the query (although locking hints are compulsory). Much like using inline or register in C++ - the compiler may choose to follow your suggestion, but it may ignore you.
|
|
|
|
|
Ok I was getting the impressing that hints was a key word in sql. Correct me if I’m wrong but when you say hints it means using something like ‘with(index=ID_Index)’, in my script. When I uses this type of statement it is telling the compiler that I think it should use this index but the compiler can over ride that if it wants.
Jason W.
|
|
|
|
|
|
This is a bit tangential: an article in August's MSDN Magazine[^] talks about generating an XML file from a SQL Server data schema.
You should then be able to use an XML parser, or XSLT, to translate into the format you want.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
