Search This Blog

Tuesday, October 26, 2010

Fetch data from tables when Database are different or DB server same.

select * from  test1.dbo.[table_1]
union all
select * from  test2.dbo.[table_1]

In this query test1 and test2 these are two different database on same server and using table table_1 & table_2 all fields are same by this query we are fetching all data from both tables.

10 comments :

Whene You Meet Me said...

thanks
it's working fine.

and when we use MYSQl

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

Anonymous said...

This is not right way. It will consume your all resource of sql server. Not recommended. Try some other way

Anonymous said...

I am requesting to anonymous user please put right way.Kindly request to you please give me solutions.

Chandra Prakash Yadav said...

I agree with this comment if you guid me this is not right way then please give solution.

Anonymous said...

I am also agree with C.P and I also request Anonymous User.
Please provide me solution.

Rahul said...

please give me solution.

Vipin said...

This is right way. I have checked it in Profiler good query. there is no other way of doing query from 2 different databases. For more optimization you can use locking and instead of '*' you can call required fields only.

Chandra Prakash Yadav said...

I agree with Vipin sir if anyone have solution then put here.......

Thanks Vipin sir for your kind support...................I hope you will always give your grate suggestion and support. I know vipin sir personally he is champ in sql..

RAJ said...

This is time for final solution.Any one give solution and stop this war...............

Chandra Prakash Yadav said...

I agree with Vipin sir if anyone have solution then put here.......

Thanks Vipin sir for your kind support...................I hope you will always give your great suggestion and support. I know vipin sir personally he is champ in sql..