SQL Query to fetch the data

navuser1navuser1 Member Posts: 1,328
edited 2014-03-12 in SQL General
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.
Now or Never

Comments

  • krikikriki Member, Moderator Posts: 9,039
    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).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Sign In or Register to comment.