|
i was Suprised , lets hear if this solves his Problem. 
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Is this what you are looking for ?
SELECT name, compatibility_level FROM sys.databases
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
|
|
|
|
|
Please help...
Is there a way when I use initcap to make any letters displayed after an apostrophe be lowercase?
thanks in advance!
|
|
|
|
|
Please i need help...
Can somebody explain me how can i implement an insertion trigger that inserts in another table.
Like i have one table wich i want to insert a new record but when i insert a record in the first table the same record must be inserted in an another table for security...
By the way im using sql server 2000 in this project.
Thanks
Sorry about the english, i speack portuguese ....
Just learning
|
|
|
|
|
Hi,
You would need something like this:
CREATE TRIGGER Duplicate ON TableName
AFTER INSERT AS
INSERT INTO AnotherTable (Column1, Column2...) SELECT Column1, Column2... FROM inserted
END
Mika
|
|
|
|
|
Oi,
Eu já fiz algumas bases de dados e nunca precisei á data, de usar triggers (em sql).
Mas queres inserir dados ao mesmo tempo em duas tabelas? Porque não fazes isso com uma store procedure normal, achas que não dá para isso?!
O que tens na ideia? talvez te possa ajudar.
|
|
|
|
|
|
We need to know what the data looks like in your file and exactly what you want when you are done with your query. It sounds like you do not have much experience with SQL so I'd suggest taking a look at some of these sites to get some basic knowledge:
SQL Tutorial[^]
SQL.org[^]
Another Tutorial[^]
Google list[^]
|
|
|
|
|
I'm having some issues with a Query I'm trying to use to fine similar records in a master table. There are several child tables that will be searched in a similar way. Basically, if there are more than two matches in the sub query I want to display those records.
Can anyone point me in the right direction to get this query to work? I understand why I can't use more than one field in my subquery but I can't think of another way to do this.
SELECT *
FROM UC
WHERE uc_key IN (SELECT mo_Key, COUNT(mo_key) as KeyCount
FROM MO
WHERE (mo_MoKey = @Key1 AND mo_Value = @Value1) OR (mo_MoKey = @Key2 AND mo_Value = @Value2) <- there will be a variable number of these that get generated programatically
AND KeyCount > 2)
(note, the table names have been mangled to protect the innocent)
Thanks, any ideas are appreciated.
|
|
|
|
|
You probably still want a JOIN; avoid using IN for anything other than literal lists.
|
|
|
|
|
Using a JOIN will improve speed?
|
|
|
|
|
It has in my experience. And much more flexible.
|
|
|
|
|
I'll have to experiment in that direction then. This is a nasty complicated app and I'll need all the little speed boosts I can get.
Thanks!
|
|
|
|
|
I can imagine. Your first task should be to eliminate the subqueries; they don't benefit from indices.
I had to change some of my JOINs the other day because the statements seemed to just sit there, now they zip along.
|
|
|
|
|
Hi,
Didn't test this so there may be typos but try something like this:
SELECT *
FROM UC
WHERE uc_key IN (SELECT mo_Key
FROM MO
WHERE (mo_MoKey = @Key1 AND mo_Value = @Value1)
OR (mo_MoKey = @Key2 AND mo_Value = @Value2)
GROUP BY mo_Key
HAVING COUNT(*) > 2);
Hope this helps,
Mika
|
|
|
|
|
Excellent, thank you for your help!
One thousand internet dollars are coming your way, just put your ethernet cable into the trash can can to catch it all.
|
|
|
|
|
|
If I understand you correctly, you just need a simple inner join.
Select ...
From YourPrimaryTable
InnerJoin YourDetailTable
on YourPrimaryTable.Column1 = YourDetailTable.Column1
and YourPrimaryTable.Column2 = YourDetailTable.Column2
...
Hope that helps.
Syed Mehroz Alam
|
|
|
|
|
Thanks for your reply. 
Can we do the same thing without taking join. I mean can we use IN clause for multiple columns?
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
Confusing...I wonder what do you need. Can you explain using a simple example.
|
|
|
|
|
Sorry to confuse you.
Let me explain you complete scenario.
I am having two databases DB1 and DB2. DB1 has single table T1 and 15 columns namely C1, C2, C3 etc. DB2 has two table T1 and T2. T1 table is identical in both the databases. So T1 table has same 15 columns and same data in both the databases. T2 has 5 columns C1, C2, C3, C4,C5 which together constitute primary key. In T1 table of DB1 database, some of the records are modified. T2 table of database DB2 has the primary keys of all the modified records. I need to update table T1 of database DB2 for the modified records.
I need a way to accomplish this. Hopefully I am clear now.
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
I got it now. This seems a difficult issue. I suggest you to create some trigger on db1.T1 that updates the modified rows in db2.
If you still want to use some batch update, the most inappropriate way will be to use a cursor loop. But wait, lets hope someone else in the forum finds a more efficient way.
Good luck.
Syed Mehroz Alam
|
|
|
|
|
You have to create any trigger or you can make changes on T1 of DB2 something like query down below which you execute that both querys at same time:
Update DB1.dbo.T1 set DB1.dbo.T1.column='value' where DB1.dbo.T1.colname ='conditionvaue'<br />
Update DB2.dbo.T1 set DB2.dbo.T1.column='value' where DB2.dbo.T1.colname ='conditionvaue'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Perhaps an inner join in the update?
UPDATE T1
SET ...
FROM T1
INNER JOIN T2
ON T1.blah=T2.blah
AND ...
|
|
|
|
|
Just one more thing that this process will happen in Batch.
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
