Post reply

Query to find similar data from many other tables

  • October 11, 2010 at 8:09 am

    #230229

    Hi All,

    I have 100 tables with the exact same column structure but different data. All these tables have one column called ReportId which include various reportids. Also, there is one more separate table (FinalTable) which has only one column called ReportId which includes various reportids which can map to the same column in all the 100 tables as I mentioned.

    I need to write a query to find -- which out of those 100 tables have atleast one or more reportids that exist in FinalTable. and if they do then what are those ReportIds.

    Thanks........

  • October 11, 2010 at 12:23 pm

    #1235174

    If I understand what you are saying, you want a list of ReportIds from all 100 tables that have a matching reportID row in the finalTable. If that is the case you could proceed using one big union select from each of the 100 tables then join to the FinalTable or you could use a cursor to join to each table separately.

    The approach depends on the upper limit of individual tables allowed in a select statement and/or whether the table names are know ahead of time versus searching for them in sysobjects at run time.

    The probability of survival is inversely proportional to the angle of arrival.

  • October 11, 2010 at 1:48 pm

    #1235201

    Thanks... I got it...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply