|
Hello, thanks for the tips, but discovered it was an index problem (as in missing a critical index).
MSDE / SQL server sure is dependant on those indexes. The equivalent problem with the exact same sized table and same query in Access would still not have degraded that far. Of course SQL server is much faster than Access once the index is there, but it's interesting how dependant it is on them for performance and how badly performance degrades if a critical one is missing.
"Things are more like they are now than they ever were before."
-- Dwight Eisenhower
|
|
|
|
|
Greetings,
I have never used triggers in SQL, and I think they could help me with a project I am working on.
So here is my question:
1.)I have 2 testing tables setup with the folowing information to just get an understanding...
(ie) MyTableA, MyTableB
MyTableA Fields
---------------
Id (Auto-number primary key).
ItemTrackNumA (used for tracking the item in table2)
TestValue (used for storing a value).
MyTableB Fields
===============
Id (Auto-number primary key)
ItemTrackNumB (used for tracking items)
LastValue (used for storing last value used)
2.) No every time I insert a value into MyTableA I want it to either add a new record in MyTableB (if
ItemTrackB is not found in the table MyTableB),or adjust the LastValue in MyTableB if ItemTrackB is
the same as ItemTrackA.
(ie) INSERT INTO MyTableA(ItemTrackNumA, TestValue)
VALUES (3, 100)
Would then perform a lookup in MyTableB and if the ItemTrackB was equal(or found) to ItemTrackA
it would just replace LastValue in MyTableB with the TestValue from MyTableA.
In this case LastValue would not have the 100 in it.
And if the ItemTrackB wasn't found than a new record would just be inserted.
I am thinking triggers would work great for this but I have so little experiance with SQL triggers that
any suggestions would be appreciated.
Thanks in advance!!
|
|
|
|
|
It sounds like you are hoping to inhibit the insert in TableA depending upon whether some data exists in TableB. I don't believe this can be done with triggers. Typically you can define the trigger to be pre and/or post insert, but even in the pre-insert situation I don't think you can stop the insert from occurring.
Chris Meech
"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.
"Microsoft hasn't ever enforced its patents. Apparently they keep them for defensive reasons only. Or, they could be waiting 'til they have a critical mass of patents, enforce them all at once and win the game of Risk that they're playing with the world." Chris Sells Feb 18, 2003.
|
|
|
|
|
Hello Chris,
That is a good point about the triggers, but in SQL server 2000 (7.0, ect) there is an available
call "INSTEAD OF", which seems to allow the ability to not have to do the insert but the problem I am
having now is accessing the params.
(ie) A simple section of code that I am checking the value to see if it is already in the table.
CREATE TRIGGER MyTrigger ON MyTableA
INSTEAD OF INSERT
AS
IF (SELECT COUNT(*)FROM MyTableA
WHERE MyTableA.Number1 = inserted.Number1) = 0
BEGIN
[...Do something...]
END
ELSE
[...Insert the new item into the database using INSERT INTO]
But the problem is "inserted.Number1" keeps saying it is undefined, and I am not sure how to get access
to the stuff that is to be inserted. I pulled the "inserted" from SQL Books online help, and I tried to
follow what they are doing, but no luck.
|
|
|
|
|
Not sure if this will help but in Oracle, you have pre-defined two variables called 'new' and 'old'. The use of them is like so
create or replace trigger transaction_create_date
before insert on transaction_table
for each row
begin
:new.create_date := sysdate;
:new.modification_date := :new.create_date;
...
Chris Meech
"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.
"Microsoft hasn't ever enforced its patents. Apparently they keep them for defensive reasons only. Or, they could be waiting 'til they have a critical mass of patents, enforce them all at once and win the game of Risk that they're playing with the world." Chris Sells Feb 18, 2003.
|
|
|
|
|
That is a cool feature of oracle! Thanks for the tip!
I did finally get it working by doing the following.
I can now check to see if any of the records have that number before it is inserted.
(ie)
CREATE TRIGGER My1stTrigger ON MyTableA
INSTEAD OF INSERT
AS
IF (SELECT COUNT(*) FROM MyTableA WHERE MyTableA.Number1 = (SELECT Number1 FROM inserted)) = 0
BEGIN
INSERT INTO MyTableA SELECT Id, Number1 FROM inserted
END
ELSE
[... Do something ...]
|
|
|
|
|
Dear friends,
I want to execute a query in SQL - Server but i want the result in variable. For this purpose i am successfully doing something like this:
DECLARE @num int<br />
SELECT @num = (select salary from mytable where id=2)<br />
PRINT @num
In above query i only select one field from the table i.e salary. Now can anyone tell me that how can i collect values of multiple fields in variables. e.g
(select name, salary from mytable where id=2)
In this case i am selecting two fields from a table i.e name, salary. Now how can i get these two values in variables. Any suggestion is welcomed.
Thanks
|
|
|
|
|
Try:
DECLARE @num int, @name nvarchar(50) -- Or whatever size you need
SELECT
@name = name,
@num = salary
FROM
mytable
WHERE
id = 2
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Hello All,
Is there a way by SQL statement, if any, to rename a table in a database?
Or there any vendor specific way of doing it?
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Try a
CREATE TABLE NEWTABLE AS (SELECT * FROM OLDTABLE)
DROP TABLE OLDTABLE
there is no simple way of renaming a table
salut,
dirk
|
|
|
|
|
Thanks so much for the tip.
DiWa wrote:
there is no simple way of renaming a table
Anything simplier than what you have shown me?
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Hello Dirk,
Back again. I have just tried the statement using OLE DB (.NET) for MS Access. It is giving an error that the CREATE statment has an error. I separated the CREATE and the DROP. Is there anything missing?
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Yes, the SQL-compatibility in ACCESS is missing. The 'CREATE TABLE AS ...' statement is not supported by the JET-Engine. Sorry, you asked for a SQL-Statement, don't know how to do real SQL in Access - hate the approach MS has to standards like SQL
The statements will work on Oracle and DB2 for sure
For Access there I don't know a way to rename a table programmatically.
best regards,
dirk
|
|
|
|
|
Thanks for the explanation. I just picked a similar statement from sql.org to copy just the table structure but it also failed.
CREATE TABLE test2 () INHERITS (test)
May be I will have to disable this option for Access.
Thanks so much for the support.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Paul Selormey wrote:
any vendor specific way
For SQL Server, you can use the sp_rename stored proc.
-- Rename the customers table to custs.
EXEC sp_rename 'customers', 'custs'
Without nipples, breasts would be pointless.
|
|
|
|
|
Wow, that is cool. Thanks.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Dear CPians,
I am developing an ASP.NET app which has a lot of database work. The app has to support both SQL Server and Access and one of the requirements is be able to switch from SQL Server to Access by setting a parameter in a XML file(the XML file is read when the app starts).
I need to use the SQLClient classes. Will I be able to use the Access DB as well through the SQLClient classes?
If yes are there any drawbacks?
If no, how can I make my app capable of accessing both DBs?
What if in the future I need to support Oracle or/and DB2 as well?
Any help anyone?
Thank you!
|---------------|
| theJazzyBrain |
|---------------|
|
|
|
|
|
The best way to handle this kind of issue in the ADO.NET is to work with the interfaces, not the classes directly.
Provide a common point to create your connection object and return, IDbConnection interface. You can you this to access the data in a provider-independent way.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
And you do I work with the interfaces? Any sample code?
If I do that, will I still have the advantages that SQLClient provides when using SQL Server. Because, the app will initially be aimed for SQL server 2000, but we are planning for a Light version which works with Access in order to cut down costs for customers.
Do you think we should try and make a component to handle all the provider-independed access?
Thanx!
Jason
|---------------|
| theJazzyBrain |
|---------------|
|
|
|
|
|
theJazzyBrain wrote:
And you do I work with the interfaces? Any sample code?
IDbConnection connection = new OleDbConnection(str);
IDbConnection connection = new SqlConnection(str);
Should work for you. You can later do something like
IDbCommand atrCommand = connection.CreateCommand();
theJazzyBrain wrote:
If I do that, will I still have the advantages that SQLClient provides when using SQL Server. Because, the app will initially be aimed for SQL server 2000, but we are planning for a Light version which works with Access in order to cut down costs for customers.
If you wish to do SQL Server or for that matter any provider specific stuff, store the connection type somehow so that you can cast the pointer to the appropriate class later.
private IDbConnection conn = null;
private Type connType = null;
SqlConnection connection = new SqlConnection(str);
conn = (IDbConnection)connection;
connType = connection.GetType();
then later, you can do something like
if (connType == typeof(SqlConnection))
SqlConnection sqlConn = (SqlConnection)conn;
else if (...)
{}
theJazzyBrain wrote:
Do you think we should try and make a component to handle all the provider-independed access?
It depends on your needs. The central point here is the connection object, so provide a means of obtaining the interface where you wish to work in provider-independent manner.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
I understand now!
This is very helpfull!
Thank you very much Paul!
Jason
|---------------|
| theJazzyBrain |
|---------------|
|
|
|
|
|
Hello all. I have a stored procedure that updates a table based on it's own contents. The table contains web site hits and I have a C# function that later on resolves the IP addresses in the table to domain names and updates them in the table.
However before that step, the stored procedure in question sees if there are already matching IP addresses from past hits that had their domains resolved and then updates them accordingly to save time on the nslookup part of it.
Problem is that the stored procedure is getting very slow when all that has changed is the number of rows in the table has increased quite a bit, however the daily number of null domains hasn't really changed at all.
I'm wondering if anyone sees anything inherently wacky or wrong with this stored procedure:
ALTER PROCEDURE dbo.zspDomainSelfUpdate<br />
AS<br />
UPDATE dbo.hits SET dbo.hits.domain = hits_1.domain<br />
FROM dbo.hits INNER JOIN<br />
dbo.hits hits_1 ON dbo.hits.ip = hits_1.ip<br />
WHERE (dbo.hits.domain IS NULL) AND (hits_1.domain IS NOT NULL)
(there is an index on the ip field)
|
|
|
|
|
Hi J,
the problem I see is the WHERE-condition, you will (almost certain) get 2 full-table scans in resolving that. I would try this:
UPDATE dbo.hits SET dbo.hits.domain =
(SELECT dbo.hits.domain FROM dbo.hits hits1
WHERE dbo.hits.ip = hits1.ip
AND dbo.domain IS NOT NULL)
WHERE dbo.hits.domain IS NULL
I guess this leads to a somewhat shorter access-plan.
good luck,
dirk
|
|
|
|
|
Thank you! I'll give that a shot, it makes sense.
|
|
|
|
|
I was reading this article:
http://www.w3schools.com/sql/sql_join.asp[^]
And it shows the following for regular joins:
<br />
SELECT Employees.Name, Orders.Product<br />
FROM Employees, Orders<br />
WHERE Employees.Employee_ID=Orders.Employee_ID<br />
and then the following for INNER JOINS:
<br />
SELECT Employees.Name, Orders.Product<br />
FROM Employees<br />
INNER JOIN Orders<br />
ON Employees.Employee_ID=Orders.Employee_ID<br />
They seem to always return the same results. Is there a difference between the two types of queries above? Or are they the same? Is there any performance reasons or any other reasons when I should favor one type of query above the other?
Thanks!
|
|
|
|