SQL Query to fetch the data

navuser1

Dear Sir,

How to get (using sql query) all the data from a single table from all the companies in a single NAV Database ?

We can use 1. Join 2.Cursor 3. CTE
Is there any thing apart from that ?
Kindly reply.

kriki

SELECT 'Company 1',f1,f2,f3 FROM SomeDatabase.dbo.[Company 1$Some Table] WHERE ....
UNION ALL
SELECT 'Company 2',f1,f2,f3 FROM SomeDatabase.dbo.[Company 2$Some Table] WHERE ....
UNION ALL
SELECT 'Company 3',f1,f2,f3 FROM SomeDatabase.dbo.[Company 3$Some Table] WHERE ....

"UNION ALL" means SQL does not check for double records between all the tables. This is faster. Use it if you are sure that there are no doubles or if there are, you don't care about it.
If you can have doubles but don't want them, use "UNION".

Use cursors ONLY if you don't have any other way to solve a problem in SQL. I don't see why a CTE would be useful in this case (except if you need to put some extra logic in it to be able to extract the data).