asked on

Merge of dynamic data into one result set - potential column differences - 18 tables into 1

Help with complex join.

I have 18 tables that I need to merge all of the data into one result set.
Each table can have a variable number of columns...some identical some not.
Each table has over 255 columns.
Each table has a requestId column with is the primary key but the primary key is unique in each of the 18 tables...so a left join doesnt do anything.

I need to be able to somehow dynamically add the rows and columns to a master result set.

I am seeing terminalogy for an full outer union ...but actually have never implemented this and am not finding good documentation.

I know how to do this the manually way of matching columns or creating aliases..but this approach will not work. Its too dynamic.

Please help. I am hoping theire is some ingenious solution out there.

Scott Pletcher

Typically you'd use a UNION ALL between each of the tables. You'd have to fill out all columns for all tables. You can use force in a NULL for a column that a particular table doesn't have.

Robb Hill

ASKER

This approach would require me to manually evaluate each of the 18 result sets and determine from one to the other where I would need a null in my select. Every one of these 18 tables are different less they do share the same primary key.

These tables have 255 + columns...and the order is all over the place.

When you say force I think you mean alias...but this would force me to get the columns in order.

As painful as this is for two tables...when you put 18 different result sets in the mix its not effecient.

Can you think of any other way to do in SQL?

Scott Pletcher

You can generate the code, you don't have to write it by hand, assuming the column names and/or position have some correspondence.

If the column names don't correspond at all, I don't know how you'd automatically dynamically match them up anyway. How would you ever know which source column matched with which destination column?

Robb Hill

ASKER

Thats part of my confusion ...

All of my tables start with the prefix AR

This query builds one massive select statement inserting nulls....essentially making a master select.

But even with this...not sure how I use this against each tables...I think my brain is hurting.

Here is this query. You copy the results of this and you have the master select....You could do an in statement but I took the shortcut with the like operator.

WITH
    AllTables (TABLE_NAME) AS (
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME LIKE 'AR%'
    ),
    TablesWithSelectors (TABLE_NAME, COLUMN_NAME, Selector) AS (
        SELECT t.TABLE_NAME, a.COLUMN_NAME, CASE WHEN b.COLUMN_NAME IS NULL THEN 'NULL AS ' ELSE '' END + a.COLUMN_NAME
        FROM AllTables t
        CROSS JOIN (SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM AllTables)) a
        LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS b ON b.TABLE_NAME = t.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME
    ),
    SelectStatements (Sql) AS (
        SELECT
            'SELECT ' +
            STUFF((
                SELECT ', ' + Selector
                FROM TablesWithSelectors
                WHERE TABLE_NAME = r.TABLE_NAME
                FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
            , 1, 2, '') +
            ' FROM ' +
            TABLE_NAME
        FROM TablesWithSelectors r
        GROUP BY TABLE_NAME
    )
SELECT STUFF((
        SELECT ' UNION ALL ' + sql
        FROM SelectStatements
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'), 1, 11, '')

Open in new window

Robb Hill

ASKER

This procedure had me on the correct track but it cuts off and doesnt get all columns in the table.

Here is an example of one of the tables:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [AccountId]
      ,[AccountType]
      ,[AnalysisOfAlternatives]
      ,[ApprCapitalExpenditure]
      ,[ApprCapitalizedInterest]
      ,[ApprOperatingExpense]
      ,[ApprovedBudgetTotal]
      ,[ARNumber2]
      ,[ARNumber3]
      ,[ARNumberLen]
      ,[ARNumberLocation]
      ,[ARType]
      ,[AssignedAssistantTreasurerNTName]
      ,[AssignedCEODisplayName]
      ,[AssignedCEONTName]
      ,[AssignedCFONTName]
      ,[AssignedCOONTName]
      ,[AssignedCreditDirectorNTName]
      ,[AssignedDeptApprover1Name]
      ,[AssignedDeptApprover1NTName]
      ,[AssignedDeptApprover2Name]
      ,[AssignedDeptApprover2NTName]
      ,[AssignedDeptApprover3Name]
      ,[AssignedDeptApprover3NTName]
      ,[AssignedDeptFPAReviewerName]
      ,[AssignedDeptFPAReviewerNTName]
      ,[AssignedDivControllerNTName]
      ,[AssignedDivOperationsNTName]
      ,[AssignedDivVPNTName]
      ,[AssignedEHSVPNTName]
      ,[AssignedFPAReviewerNTName]
      ,[AssignedITDirectorNTName]
      ,[AssignedITReviewerNTName]
      ,[AssignedOperationController]
      ,[AssignedProjectManager]
      ,[AssignedProjectManager2]
      ,[AssignedProjectManager3]
      ,[AssignedProjectManager4]
      ,[AssignedProjectManager5]
      ,[AssignedSalesManager]
      ,[AssignedSiteManager]
      ,[AssignedTechnicalManager]
      ,[AssignedTreasuryFinanceNTName]
      ,[AssignedVPFinanceNTName]
      ,[AssignedVPSales]
      ,[AssignedVPTreasuryNTName]
      ,[AssistantTreasurerApprovalStatus]
      ,[AssistantTreasurerAuthBy]
      ,[AssistantTreasurerAuthDate]
      ,[AssistantTreasurerComments]
      ,[AssumptionsExpectedBenefits]
      ,[AuthorizedUser]
      ,[Background]
      ,[BusinessUnitMessage]
      ,[CEOApprovalStatus]
      ,[CEOAuthBy]
      ,[CEOAuthDate]
      ,[CEOAuthLimit]
      ,[CEOComments]
      ,[CFOApprovalStatus]
      ,[CFOAuthBy]
      ,[CFOAuthDate]
      ,[CFOAuthLimit]
      ,[CFOComments]
      ,[COOApprovalStatus]
      ,[COOAuthBy]
      ,[COOAuthDate]
      ,[COOComments]
      ,[CorporateAdministrator]
      ,[CorporateCostCenter]
      ,[CorporateDept]
      ,[CreateDate]
      ,[CreditDirectorApprovalStatus]
      ,[CreditDirectorAuthBy]
      ,[CreditDirectorAuthDate]
      ,[CreditDirectorComments]
      ,[CurrentLoginUser]
      ,[DeptApprover1ApprovalStatus]
      ,[DeptApprover1AuthBy]
      ,[DeptApprover1AuthDate]
      ,[DeptApprover1Comments]
      ,[DeptApprover2ApprovalStatus]
      ,[DeptApprover2AuthBy]
      ,[DeptApprover2AuthDate]
      ,[DeptApprover2Comments]
      ,[DeptApprover3ApprovalStatus]
      ,[DeptApprover3AuthBy]
      ,[DeptApprover3AuthDate]
      ,[DeptApprover3Comments]
      ,[DeptFPAReviewerApprovalStatus]
      ,[DeptFPAReviewerAuthBy]
      ,[DeptFPAReviewerAuthDate]
      ,[DeptFPAReviewerComments]
      ,[DiscountRate]
      ,[DisplayName]
      ,[DivControllerApprovalStatus]
      ,[DivControllerAuthBy]
      ,[DivControllerAuthDate]
      ,[DivControllerComments]
      ,[DivisionAdminNTName]
      ,[DivisionVPAuthLimit]
      ,[DivisionVPComments]
      ,[DivOperationsApprovalStatus]
      ,[DivOperationsAuthBy]
      ,[DivOperationsAuthDate]
      ,[DivOperationsComments]
      ,[DivVPApprovalStatus]
      ,[DivVPAuthBy]
      ,[DivVPAuthDate]
      ,[EHSVPApprovalGroup]
      ,[EHSVPApprovalStatus]
      ,[EHSVPAuthBy]
      ,[EHSVPAuthDate]
      ,[EHSVPComments]
      ,[EstimatedUsefulLife]
      ,[FASTestPerformed]
      ,[FPAReviewerApprovalStatus]
      ,[FPAReviewerAuthBy]
      ,[FPAReviewerAuthDate]
      ,[FPAReviewerComments]
      ,[group1]
      ,[InternalRateofReturn]
      ,[ITDirectorApprovalStatus]
      ,[ITDirectorAuthBy]
      ,[ITDirectorAuthDate]
      ,[ITDirectorComments]
      ,[ITReviewerApprovalStatus]
      ,[ITReviewerAuthBy]
      ,[ITReviewerAuthDate]
      ,[ITReviewerComments]
      ,[LeaseTerm]
      ,[LeaseTermText]
      ,[Location]
      ,[LocationMessage]
      ,[MIRR]
      ,[MIRRText]
      ,[NeedEHSVPApproval]
      ,[NeedResubmit]
      ,[NeedsITWork]
      ,[NetPresentValue]
      ,[NetPresentValueRequired]
      ,[NumOfAttach]
      ,[OBAddCapacity]
      ,[OBCostReduction]
      ,[OBEnvironmental]
      ,[OBMaintenanceRepair]
      ,[OBProductivity]
      ,[OBQuality]
      ,[OBRequired]
      ,[OBRequiredTemp1]
      ,[OBRequiredTemp2]
      ,[OBRIMIImpulse]
      ,[OBSafetyIndustrialRelations]
      ,[OperationalBenefits]
      ,[OperationControllerApprovalStatus]
      ,[OperationControllerAuthBy]
      ,[OperationControllerAuthDate]
      ,[OperationControllerComments]
      ,[OperationControllerNTName]
      ,[PaybackPeriod]
      ,[PlantDivDept]
      ,[PreparedBy]
      ,[PresentCondition]
      ,[ProjectManager2ApprovalStatus]
      ,[ProjectManager2AuthBy]
      ,[ProjectManager2AuthDate]
      ,[ProjectManager2Comments]
      ,[ProjectManager2NTName]
      ,[ProjectManager3ApprovalStatus]
      ,[ProjectManager3AuthBy]
      ,[ProjectManager3AuthDate]
      ,[ProjectManager3Comments]
      ,[ProjectManager3NTName]
      ,[ProjectManager4ApprovalStatus]
      ,[ProjectManager4AuthBy]
      ,[ProjectManager4AuthDate]
      ,[ProjectManager4Comments]
      ,[ProjectManager4NTName]
      ,[ProjectManager5ApprovalStatus]
      ,[ProjectManager5AuthBy]
      ,[ProjectManager5AuthDate]
      ,[ProjectManager5Comments]
      ,[ProjectManager5NTName]
      ,[ProjectManagerApprovalStatus]
      ,[ProjectManagerAuthBy]
      ,[ProjectManagerAuthDate]
      ,[ProjectManagerComments]
      ,[ProjectManagerNTName]
      ,[ProjectName]
      ,[ProjectStartDate]
      ,[ProposedBegDate]
      ,[ProposedCompletionDate]
      ,[ProposedCondition]
      ,[PurposeOfProject]
      ,[QuestionAndAnswerGroup]
      ,[ReqCapitalExpenditure]
      ,[ReqCapitalizedInterest]
      ,[ReqOperatingExpense]
      ,[RequestID]
      ,[RequestorEmail]
      ,[RequestorNTName]
      ,[RequestTotal]
      ,[RequiredText]
      ,[Revision]
      ,[RouteToCEO]
      ,[RoutingStatus]
      ,[SalesManagerApprovalReq]
      ,[SalesManagerApprovalStatus]
      ,[SalesManagerAuthBy]
      ,[SalesManagerAuthDate]
      ,[SalesManagerComments]
      ,[SalesManagerNTName]
      ,[SalesManagerRequiredText]
      ,[SaveForLater]
      ,[SavingPerMonth]
      ,[SiteManagerApprovalStatus]
      ,[SiteManagerAuthBy]
      ,[SiteManagerAuthDate]
      ,[SiteManagerAuthLimit]
      ,[SiteManagerComments]
      ,[SiteManagerNTName]
      ,[SubmissionDate]
      ,[Submited]
      ,[SubmitGroupChange]
      ,[SubmitGroupNew]
      ,[TechnicalManagerApprovalStatus]
      ,[TechnicalManagerAuthBy]
      ,[TechnicalManagerAuthDate]
      ,[TechnicalManagerComments]
      ,[TechnicalManagerNTName]
      ,[TotalLeaseRevenues]
      ,[TotalLeaseRevenuesgText]
      ,[TotalProjectCost]
      ,[TreasuryFinanceApprovalStatus]
      ,[TreasuryFinanceAuthBy]
      ,[TreasuryFinanceAuthDate]
      ,[TreasuryFinanceComments]
      ,[VPFinanceApprovalStatus]
      ,[VPFinanceAuthBy]
      ,[VPFinanceAuthDate]
      ,[VPFinanceAuthLimit]
      ,[VPFinanceComments]
      ,[VPSalesApprovalStatus]
      ,[VPSalesAuthBy]
      ,[VPSalesAuthDate]
      ,[VPSalesComments]
      ,[VPSalesNTName]
      ,[VPSalesRequiredText]
      ,[VPTreasuryApprovalStatus]
      ,[VPTreasuryAuthBy]
      ,[VPTreasuryAuthDate]
      ,[VPTreasuryComments]
      ,[ProjectDescription]
      ,[OBOther]
  FROM [SharePoint2007Archive].[dbo].[AR_Archive9]

Open in new window

You can see what it does here:

FROM AR_Archive18
UNION ALL
SELECT AccountId, AccountType, AddtlCorpApproverApprovalStatus, AddtlCorpApproverAuthBy, AddtlCorpApproverAuthDate, AddtlCorpApproverComments, AddtlCorpApproverNTName, AnalysisOfAlternatives, ApprCapitalExpenditure, ApprCapitalizedInterest, ApprOperatingExpense, ApprovedBudgetTotal, ARNumber2, ARNumber3, ARNumberLen, ARNumberLocation, ARType, AssignedAddtlCorpApprover, AssignedAssistantTreasurerName, AssignedAssistantTreasurerNTName, AssignedCEODisplayName, AssignedCEONTName, AssignedCFOName, AssignedCFONTName, AssignedCOONTName, AssignedCreditDirectorName, AssignedCreditDirectorNTName, AssignedDeptApprover1Name, AssignedDeptApprover1NTName, AssignedDeptApprover2Name, AssignedDeptApprover2NTName, AssignedDeptApprover3Name, AssignedDeptApprover3NTName, NULL AS AssignedDeptApprover4Name, NULL AS AssignedDeptApprover4NTName, AssignedDeptFPAReviewerName, AssignedDeptFPAReviewerNTName, AssignedDivControllerName, AssignedDivControllerNTName, AssignedDivOperationsName, AssignedDivOperationsNTName, AssignedDivVPName, AssignedDivVPNTName, AssignedEHSVPName, AssignedEHSVPNTName, AssignedFPAReviewerName, AssignedFPAReviewerNTName, AssignedITDirectorName, AssignedITDirectorNTName, NULL AS AssignedITReviewerApprovalStatus, NULL AS AssignedITReviewerAuthBy, NULL AS AssignedITReviewerAuthDate, NULL AS AssignedITReviewerComments, AssignedITReviewerName, AssignedITReviewerNTName, AssignedOperationController, AssignedProjectManager, AssignedProjectManager2, AssignedProjectManager3, AssignedProjectManager4, AssignedProjectManager5, AssignedSalesManager, AssignedSiteManager, AssignedTechnicalManager, AssignedTreasuryFinanceName, AssignedTreasuryFinanceNTName, NULL AS AssignedTreasuryReviewerNTName, AssignedVPFinanceName, AssignedVPFinanceNTName, AssignedVPSales, AssignedVPTreasuryName, AssignedVPTreasuryNTName, AssistantTreasurerApprovalStatus, AssistantTreasurerAuthBy, AssistantTreasurerAuthDate, AssistantTreasurerComments, AssumptionsExpectedBenefits, AuthorizedUser, Background, BusinessUnitMessage, CEOApprovalStatus, CEOAuthBy, CEOAuthDate, CEOAuthLimit, CEOComments, CFOApprovalStatus, CFOAuthBy, CFOAuthDate, CFOAuthLimit, CFOComments, COOApprovalStatus, COOAuthBy, COOAuthDate, COOComments, CorporateAdministrator, CorporateCostCenter, CorporateDept, CreateDate, CreditDirectorApprovalStatus, CreditDirectorAuthBy, CreditDirectorAuthDate, CreditDirectorComments, CurrentLoginUser, NULL AS DepartmentApprover4Group, DeptApprover1ApprovalStatus, DeptApprover1AuthBy, DeptApprover1AuthDate, DeptApprover1Comments, DeptApprover2ApprovalStatus, DeptApprover2AuthBy, DeptApprover2AuthDate, DeptApprover2Comments, DeptApprover3ApprovalStatus, DeptApprover3AuthBy, DeptApprover3AuthDate, DeptApprover3Comments, NULL AS DeptApprover4ApprovalStatus, NULL AS DeptApprover4AuthBy, NULL AS DeptApprover4AuthDate, NULL AS DeptApprover4Comments, DeptFPAReviewerApprovalStatus, DeptFPAReviewerAuthBy, DeptFPAReviewerAuthDate, DeptFPAReviewerComments, DiscountRate, DisplayName, DivControllerApprovalStatus, DivControllerAuthBy, DivControllerAuthDate, DivControllerComments, DivisionAdminNTName, DivisionOnly, DivisionVPAuthLimit, DivisionVPComments, DivOperationsApprovalStatus, DivOperationsAuthBy, DivOperationsAuthDate, DivOperationsComments, DivVPApprovalStatus, DivVPAuthBy, DivVPAuthDate, EHSVPApprovalGroup, EHSVPApprovalStatus, EHSVPAuthBy, EHSVPAuthDate, EHSVPComments, EstimatedUsefulLife, FASTestPerformed, FPAReviewerApprovalStatus, FPAReviewerAuthBy, FPAReviewerAuthDate, FPAReviewerComments, group1, InternalRateofReturn, ITDirectorApprovalStatus, ITDirectorAuthBy, ITDirectorAuthDate, ITDirectorComments, ITReviewerApprovalStatus, ITReviewerAuthBy, ITReviewerAuthDate, ITReviewerComments, LeaseTerm, LeaseTermText, NULL AS li, Location, LocationMessage, MIRR, MIRRText, NeedEHSVPApproval, NeedResubmit, NeedsITWork, NetPresentValue, NetPresentValueRequired, NumOfAttach, OBAddCapacity, OBCostReduction, OBEnvironmental, OBMaintenanceRepair, OBOther, OBProductivity, OBQuality, OBRequired, OBRequiredTemp1, OBRequiredTemp2, OBRIMIImpulse, OBSafetyIndustrialRelations, OperationalBenefits, OperationControllerApprovalStatus, OperationControllerAuthBy, OperationControllerAuthDate, OperationControllerComments, OperationControllerNTName, PaybackPeriod, PlantDivDept, PreparedBy, PresentCondition, ProjectDescription, ProjectManager2ApprovalStatus, ProjectManager2AuthBy, ProjectManager2AuthDate, ProjectManager2Comments, ProjectManager2NTName, ProjectManager3ApprovalStatus, ProjectManager3AuthBy, ProjectManager3AuthDate, ProjectManager3Comments, ProjectManager3NTName, ProjectManager4ApprovalStatus, ProjectManager4AuthBy, ProjectManager4AuthDate, ProjectManager4Comments, ProjectManager4NTName, ProjectManager5ApprovalStatus, ProjectManager5AuthBy, ProjectManager5AuthDate, ProjectManager5Comments, ProjectManager5NTName, ProjectManagerApprovalStatus, ProjectManagerAuthBy, ProjectManagerAuthDate, ProjectManagerComments, ProjectManagerNTName, ProjectName, ProjectStartDate, ProposedBegDate, ProposedCompletionDate, ProposedCondition, PurposeOfProject, QuestionAndAnswerGroup, ReqCapitalExpenditure, ReqCapitalizedInterest, ReqOperatingExpense, RequestID, RequestorEmail, RequestorNTName, RequestTotal, RequiredText, Revision, RouteToCEO, RoutingStatus, SalesManagerApprovalReq, SalesManagerApprovalStatus, SalesManagerAuthBy, SalesManagerAuthDate, SalesManagerComments, SalesManagerNTName, SalesManagerRequiredText, SaveForLater, SavingPerMonth, SiteManagerApprovalStatus, SiteManagerAuthBy, SiteManagerAuthDate, SiteManagerAuthLimit, SiteManagerComments, SiteManagerNTName, SubmissionDate, Submited, SubmitGroupChange, SubmitGroupNew, TechnicalManagerApprovalStatus, TechnicalManagerAuthBy, TechnicalManagerAuthDate, TechnicalManagerComments, TechnicalManagerNTName, TotalLeaseRevenues, TotalLeaseRevenuesgText, TotalProjectCost, TreasuryFinanceApprovalStatus, TreasuryFinanceAuthBy, TreasuryFinanceAuthDate, TreasuryFinanceComments, NULL AS TreasuryReviewerApprovalStatus, NULL AS TreasuryReviewerAuthBy, NULL AS TreasuryReviewerAuthDate, NULL AS TreasuryReviewerComments, NULL AS TreasuryReviewerGroup, NULL AS ul, VPFinanceApprovalStatus, VPFinanceAuthBy, VPFinanceAuthDate, VPFinanceAuthLimit, VPFinanceComments, VPSalesApprovalStatus, VPSalesAuthBy, VPSalesAuthDate, VPSalesComments, VPSalesNTName, VPSalesRequiredText, VPTreasuryApprovalStatus, VPTreasuryAuthBy, VPTreasuryAuthDate, VPTreasuryComments FROM AR UNION ALL SELECT NULL AS AccountId, NULL AS AccountType, NULL AS AddtlCorpApproverApprovalStatus, NULL AS AddtlCorpApproverAuthBy, NULL AS AddtlCorpApproverAuthDate, NULL AS AddtlCorpApproverComments, NULL AS AddtlCorpApproverNTName, NULL AS AnalysisOfAlternatives, ApprCapitalExpenditure, ApprCapitalizedInterest, ApprOperatingExpense, ApprovedBudgetTotal, ARNumber2, ARNumber3, ARNumberLen, ARNumberLocation, NULL AS ARType, NULL AS AssignedAddtlCorpApprover, NULL AS AssignedAssistantTreasurerName, NULL AS AssignedAssistantTreasurerNTName, NULL AS AssignedCEODisplayName, AssignedCEONTName, NULL AS AssignedCFOName, AssignedCFONTName, AssignedCOONTName, NULL AS AssignedCreditDirectorName, AssignedCreditDirectorNTName, NULL AS AssignedDeptApprover1Name, NULL AS AssignedDeptApprover1NTName, NULL AS AssignedDeptApprover2Name, NULL AS AssignedDeptApprover2NTName, NULL AS AssignedDeptApprover3Name, NULL AS AssignedDeptApprover3NTName, NULL AS AssignedDeptApprover4Name, NULL AS AssignedDeptApprover4NTName, NULL AS AssignedDeptFPAReviewerName, NULL AS AssignedDeptFPAReviewerNTName, NULL AS AssignedDivControllerName, AssignedDivControllerNTName, NULL AS AssignedDivOperationsName, AssignedDivOperationsNTName, NULL AS AssignedDivVPName, AssignedDivVPNTName, NULL AS AssignedEHSVPName, AssignedEHSVPNTName, NULL AS AssignedFPAReviewerName, AssignedFPAReviewerNTName, NULL AS AssignedITDirectorName, AssignedITDirectorNTName, NULL AS AssignedITReviewerApprovalStatus, NULL AS AssignedITReviewerAuthBy, NULL AS AssignedITReviewerAuthDate, NULL AS AssignedITReviewerComments, NULL AS AssignedITReviewerName, NULL AS AssignedITReviewerNTName, AssignedOperationController, AssignedProjectManager, NULL AS AssignedProjectManager2, NULL AS AssignedProjectManager3, NULL AS AssignedProjectManager4, NULL AS AssignedProjectManager5, AssignedSalesManager, AssignedSiteManager, AssignedTechnicalManager, NULL AS AssignedTreasuryFinanceName, NULL AS AssignedTreasuryFinanceNTName, AssignedTreasuryReviewerNTName, NULL AS AssignedVPFinanceName, AssignedVPFinanceNTName, NULL AS AssignedVPSales, NULL AS AssignedVPTreasuryName, AssignedVPTreasuryNTName, NULL AS AssistantTreasurerApprovalStatus, NULL AS AssistantTreasurerAuthBy, NULL AS AssistantTreasurerAuthDate, NULL AS AssistantTreasurerComments, NULL AS AssumptionsExpectedBenefits, NULL AS AuthorizedUser, NULL AS Background, BusinessUnitMessage, CEOApprovalStatus, CEOAuthBy, CEOAuthDate, CEOAuthLimit, CEOComments, CFOApprovalStatus, CFOAuthBy, CFOAuthDate, CFOAuthLimit, CFOComments, COOApprovalStatus, COOAuthBy, COOAuthDate, COOComments, NULL AS CorporateAdministrator, NULL AS CorporateCostCenter, CorporateDept, CreateDate, CreditDirectorApprovalStatus, CreditDirectorAuthBy, CreditDirectorAuthDate, CreditDirectorComments, NULL AS CurrentLoginUser, NULL AS DepartmentApprover4Group, NULL AS DeptApprover1ApprovalStatus, NULL AS DeptApprover1AuthBy, NULL AS DeptApprover1AuthDate, NULL AS DeptApprover1Comments, NULL AS DeptApprover2ApprovalStatus, NULL AS DeptApprover2AuthBy, NULL AS DeptApprover2AuthDate, NULL AS DeptApprover2Comments, NULL AS DeptApprover3ApprovalStatus, NULL AS DeptApprover3AuthBy, NULL AS DeptApprover3AuthDate, NULL AS DeptApprover3Comments, NULL AS DeptApprover4ApprovalStatus, NULL AS DeptApprover4AuthBy, NULL AS DeptApprover4AuthDate, NULL AS DeptApprover4Comments, NULL AS DeptFPAReviewerApprovalStatus, NULL AS DeptFPAReviewerAuthBy, NULL AS DeptFPAReviewerAuthDate, NULL AS DeptFPAReviewerComments, DiscountRate, NULL AS DisplayName, DivControllerApprovalStatus, DivControllerAuthBy, DivControllerAuthDate, DivControllerComments, NULL AS DivisionAdminNTName, NULL AS DivisionOnly, DivisionVPAuthLimit, DivisionVPComments, DivOperationsApprovalStatus, DivOperationsAuthBy, DivOperationsAuthDate, DivOperationsComments, DivVPApprovalStatus, DivVPAuthBy, DivVPAuthDate, EHSVPApprovalGroup, EHSVPApprovalStatus, EHSVPAuthBy, EHSVPAuthDate, EHSVPComments, EstimatedUsefulLife, FASTestPerformed, FPAReviewerApprovalStatus, FPAReviewerAuthBy, FPAReviewerAuthDate, FPAReviewerComments, group1, InternalRateofReturn, ITDirectorApprovalStatus, ITDirectorAuthBy, ITDirectorAuthDate, ITDirectorComments, NULL AS ITReviewerApprovalStatus, NULL AS ITReviewerAuthBy, NULL AS ITReviewerAuthDate, NULL AS ITReviewerComments, LeaseTerm, LeaseTermText, NULL AS li, Location, LocationMessage, MIRR, MIRRText, NeedEHSVPApproval, NeedResubmit, NeedsITWork, NetPresentValue, NetPresentValueRequired, NumOfAttach, OBAddCapacity, OBCostReduction, OBEnvironmental, OBMaintenanceRepair, OBOther, OBProductivity, OBQuality, OBRequired, OBRequiredTemp1, OBRequiredTemp2, OBRIMIImpulse, OBSafetyIndustrialRelations, OperationalBenefits, OperationControllerApprovalStatus, OperationControllerAuthBy, OperationControllerAuthDate, OperationControllerComments, OperationControllerNTName, PaybackPeriod, PlantDivDept, PreparedBy, NULL AS PresentCondition, ProjectDescription, NULL AS ProjectManager2ApprovalStatus, NULL AS ProjectManager2AuthBy, NULL AS ProjectManager2AuthDate, NULL AS ProjectManager2Comments, NULL AS ProjectManager2NTName, NULL AS ProjectManager3ApprovalStatus, NULL AS ProjectManager3AuthBy, NULL AS ProjectManager3AuthDate, NULL AS ProjectManager3Comments, NULL AS ProjectManager3NTName, NULL AS ProjectManager4ApprovalStatus, NULL AS ProjectManager4AuthBy, NULL AS ProjectManager4AuthDate, NULL AS ProjectManager4Comments, NULL AS ProjectManager4NTName, NULL AS ProjectManager5ApprovalStatus, NULL AS ProjectManager5AuthBy, NULL AS ProjectManager5AuthDate, NULL AS ProjectManager5Comments, NULL AS ProjectManager5NTName, ProjectManagerApprovalStatus, ProjectManagerAuthBy, ProjectManagerAuthDate, ProjectManagerComments, ProjectManagerNTName, ProjectName, ProjectStartDate, ProposedBegDate, ProposedCompletionDate, NULL AS ProposedCondition, NULL AS PurposeOfProject, QuestionAndAnswerGroup, ReqCapitalExpenditure, ReqCapitalizedInterest, ReqOperatingExpense, RequestID, RequestorEmail, RequestorNTName, RequestTotal, RequiredText, Revision, NULL AS RouteToCEO, RoutingStatus, SalesManagerApprovalReq, SalesManagerApprovalStatus, SalesManagerAuthBy, SalesManagerAuthDate, SalesManagerComments, SalesManagerNTName, NULL AS SalesManagerRequiredText, SaveForLater, SavingPerMonth, SiteManagerApprovalStatus, SiteManagerAuthBy, SiteManagerAuthDate, SiteManagerAuthLimit, SiteManagerComments, SiteManagerNTName, SubmissionDate, Submited, SubmitGroupChange, SubmitGroupNew, TechnicalManagerApprovalStatus, TechnicalManagerAuthBy, TechnicalManagerAuthDate, TechnicalManagerComments, TechnicalManagerNTName, TotalLeaseRevenues, TotalLeaseRevenuesgText, TotalProjectCost, NULL AS TreasuryFinanceApprovalStatus, NULL AS TreasuryFinanceAuthBy, NULL AS TreasuryFinanceAuthDate, NULL AS TreasuryFinanceComments, TreasuryReviewerApprovalStatus, TreasuryReviewerAuthBy, TreasuryReviewerAuthDate, TreasuryReviewerComments, TreasuryReviewerGroup, NULL AS ul, VPFinanceApprovalStatus, VPFinanceAuthBy, VPFinanceAuthDate, VPFinanceAuthLimit, VPFinanceComments, NULL AS VPSalesApprovalStatus, NULL AS VPSalesAuthBy, NULL AS VPSalesAuthDate, NULL AS VPSalesComments, NULL AS VPSalesNTName, NULL AS VPSalesRequiredText, VPTreasuryApprovalStatus, VPTreasuryAuthBy, VPTreasuryAuthDate, VPTreasuryComments FROM AR_Archive1 UNION ALL SELECT NULL AS AccountId, NULL AS AccountType, NULL AS AddtlCorpApproverApprovalStatus, NULL AS AddtlCorpApproverAuthBy, NULL AS AddtlCorpApproverAuthDate, NULL AS AddtlCorpApproverComments, NULL AS AddtlCorpApproverNTName, NULL AS AnalysisOfAlternatives, ApprCapitalExpenditure, ApprCapitalizedInterest, ApprOperatingExpense, ApprovedBudgetTotal, ARNumber2, ARNumber3, ARNumberLen, ARNumberLocation, NULL AS ARType, NULL AS AssignedAddtlCorpApprover, NULL AS AssignedAssistantTreasurerName, NULL AS AssignedAssistantTreasurerNTName, NULL AS AssignedCEODisplayName, AssignedCEONTName, NULL AS AssignedCFOName, AssignedCFONTName, AssignedCOONTName, NULL AS AssignedCreditDirectorName, AssignedCreditDirectorNTName, NULL AS AssignedDeptApprover1Name, NULL AS AssignedDeptApprover1NTName, NULL AS AssignedDeptApprover2Name, NULL AS AssignedDeptApprover2NTName, NULL AS AssignedDeptApprover3Name, NULL AS AssignedDeptApprover3NTName, NULL AS AssignedDeptApprover4Name, NULL AS AssignedDeptApprover4NTName, NULL AS AssignedDeptFPAReviewerName, NULL AS AssignedDeptFPAReviewerNTName, NULL AS AssignedDivControllerName, AssignedDivControllerNTName, NULL AS AssignedDivOperationsName, AssignedDivOperationsNTName, NULL AS AssignedDivVPName, AssignedDivVPNTName, NULL AS AssignedEHSVPName, AssignedEHSVPNTName, NULL AS AssignedFPAReviewerName, AssignedFPAReviewerNTName, NULL AS AssignedITDirectorName, AssignedITDirectorNTName, AssignedITReviewerApprovalStatus, AssignedITReviewerAuthBy, AssignedITReviewerAuthDate, AssignedITReviewerComments, NULL AS AssignedITReviewerName, AssignedITReviewerNTName, AssignedOperationController, AssignedProjectManager, AssignedProjectManager2, AssignedProjectManager3, AssignedProjectManager4, AssignedProjectManager5, AssignedSalesManager, AssignedSiteManager, AssignedTechnicalManager, NULL AS AssignedTreasuryFinanceName, NULL AS AssignedTreasuryFinanceNTName, AssignedTreasuryReviewerNTName, NULL AS AssignedVPFinanceName, AssignedVPFinanceNTName, NULL AS AssignedVPSales, NULL AS AssignedVPTreasuryName, AssignedVPTreasuryNTName, NULL AS AssistantTreasurerApprovalStatus, NULL AS AssistantTreasurerAuthBy, NULL AS AssistantTreasurerAuthDate, NULL AS AssistantTreasurerComments, NULL AS AssumptionsExpectedBenefits, AuthorizedUser, NULL AS Background, BusinessUnitMessage, CEOApprovalStatus, CEOAuthBy, CEOAuthDate, CEOAuthLimit, CEOComments, CFOApprovalStatus, CFOAuthBy, CFOAuthDate, CFOAuthLimit, CFOComments, COOApprovalStatus, COOAuthBy, COOAuthDate, COOComments, CorporateAdministrator, NULL AS CorporateCostCenter, CorporateDept, CreateDate, CreditDirectorApprovalStatus, CreditDirectorAuthBy, CreditDirectorAuthDate, CreditDirectorComments, CurrentLoginUser, NULL AS DepartmentApprover4Group, NULL AS DeptApprover1ApprovalStatus, NULL AS DeptApprover1AuthBy, NULL AS DeptApprover1AuthDate, NULL AS DeptApprover1Comments, NULL AS DeptApprover2ApprovalStatus, NULL AS DeptApprover2AuthBy, NULL AS DeptApprover2AuthDate, NULL AS DeptApprover2Comments, NULL AS DeptApprover3ApprovalStatus, NULL AS DeptApprover3AuthBy, NULL AS DeptApprover3AuthDate, NULL AS DeptApprover3Comments, NULL AS DeptApprover4ApprovalStatus, NULL AS DeptApprover4AuthBy, NULL AS DeptApprover4AuthDate, NULL AS DeptApprover4Comments, NULL AS DeptFPAReviewerApprovalStatus, NULL AS DeptFPAReviewerAuthBy, NULL AS DeptFPAReviewerAuthDate, NULL AS DeptFPAReviewerComments, DiscountRate, NULL AS DisplayName, DivControllerApprovalStatus, DivControllerAuthBy, DivControllerAuthDate, DivControllerComments, DivisionAdminNTName, NULL AS DivisionOnly, DivisionVPAuthLimit, DivisionVPComments, DivOperationsApprovalStatus, DivOperationsAuthBy, DivOperationsAuthDate, DivOperationsComments, DivVPApprovalStatus, DivVPAuthBy, DivVPAuthDate, EHSVPApprovalGroup, EHSVPApprovalStatus, EHSVPAuthBy, EHSVPAuthDate, EHSVPComments, EstimatedUsefulLife, FASTestPerformed, FPAReviewerApprovalStatus, FPAReviewerAuthBy, FPAReviewerAuthDate, FPAReviewerComments, group1, InternalRateofReturn, ITDirectorApprovalStatus, ITDirectorAuthBy, ITDirectorAuthDate, ITDirectorComments, NULL AS ITReviewerApprovalStatus, NULL AS ITReviewerAuthBy, NULL AS ITReviewerAuthDate, NULL AS ITReviewerComments, LeaseTerm, LeaseTermText, NULL AS li, Location, LocationMessage, MIRR, MIRRText, NeedEHSVPApproval, NeedResubmit, NeedsITWork, NetPresentValue, NetPresentValueRequired, NumOfAttach, OBAddCapacity, OBCostReduction, OBEnvironmental, OBMaintenanceRepair, OBOther, OBProductivity, OBQuality, OBRequired, OBRequiredTemp1, OBRequiredTemp2, OBRIMIImpulse, OBSafetyIndustrialRelations, OperationalBenefits, OperationControllerApprovalStatus, OperationControllerAuthBy, OperationControllerAuthDate, OperationControllerComments, OperationControllerNTName, PaybackPeriod, PlantDivDept, PreparedBy, NULL AS PresentCondition, ProjectDescription, ProjectManager2ApprovalStatus, ProjectManager2AuthBy, ProjectManager2AuthDate, ProjectManager2Comments, ProjectManager2NTName, ProjectManager3ApprovalStatus, ProjectManager3AuthBy, ProjectManager3AuthDate, ProjectManager3Comments, ProjectManager3NTName, ProjectManager4ApprovalStatus, ProjectManager4AuthBy, ProjectManager4AuthDate, ProjectManager4Comments, ProjectManager4NTName, ProjectManager5ApprovalStatus, ProjectManager5AuthBy, ProjectManager5AuthDate, ProjectManager5Comments, ProjectManager5NTName, ProjectManagerApprovalStatus, ProjectManagerAuthBy, ProjectManagerAuthDate, ProjectManagerComments, ProjectManagerNTName, ProjectName, ProjectStartDate, ProposedBegDate, ProposedCompletionDate, NULL AS ProposedCondition, NULL AS PurposeOfProject, QuestionAndAnswerGroup, ReqCapitalExpenditure, ReqCapitalizedInterest, ReqOperatingExpense, RequestID, RequestorEmail, RequestorNTName, RequestTotal, RequiredText, Revision, NULL AS RouteToCEO, RoutingStatus, SalesManagerApprovalReq, SalesManagerApprovalStatus, SalesManagerAuthBy, SalesManagerAuthDate, SalesManagerComments, SalesManagerNTName, NULL AS SalesManagerRequiredText, SaveForLater, SavingPerMonth, SiteManagerApprovalStatus, SiteManagerAuthBy, SiteManagerAuthDate, SiteManagerAuthLimit, SiteManagerComments, SiteManagerNTName, SubmissionDate, Submited, SubmitGroupChange, SubmitGroupNew, TechnicalManagerApprovalStatus, TechnicalManagerAuthBy, TechnicalManagerAuthDate, TechnicalManagerComments, TechnicalManagerNTName, TotalLeaseRevenues, TotalLeaseRevenuesgText, TotalProjectCost, NULL AS TreasuryFinanceApprovalStatus, NULL AS TreasuryFinanceAuthBy, NULL AS TreasuryFinanceAuthDate, NULL AS TreasuryFinanceComments, TreasuryReviewerApprovalStatus, TreasuryReviewerAuthBy, TreasuryReviewerAuthDate, TreasuryReviewerComments, TreasuryReviewerGroup, NULL AS ul, VPFinanceApprovalStatus, VPFinanceAuthBy, VPFinanceAuthDate, VPFinanceAuthLimit, VPFinanceComments, NULL AS VPSalesApprovalStatus, NULL AS VPSalesAuthBy, NULL AS VPSalesAuthDate, NULL AS VPSalesComments, NULL AS VPSalesNTName, NULL AS VPSalesRequiredText, VPTreasuryApprovalStatus, VPTreasuryAuthBy, VPTreasuryAuthDate, VPTreasuryComments FROM AR_Archive10 UNION ALL SELECT NULL AS AccountId, NULL AS AccountType, NULL AS AddtlCorpApproverApprovalStatus, NULL AS AddtlCorpApproverAuthBy, NULL AS AddtlCorpApproverAuthDate, NULL AS AddtlCorpApproverComments, NULL AS AddtlCorpApproverNTName, NULL AS AnalysisOfAlternatives, ApprCapitalExpenditure, ApprCapitalizedInterest, ApprOperatingExpense, ApprovedBudgetTotal, ARNumber2, ARNumber3, ARNumberLen, ARNumberLocation, ARType, NULL AS AssignedAddtlCorpApprover, NULL AS AssignedAssistantTreasurerName, NULL AS AssignedAssistantTreasurerNTName, NULL AS AssignedCEODisplayName, AssignedCEONTName, NULL AS AssignedCFOName, AssignedCFONTName, AssignedCOONTName, NULL AS AssignedCreditDirectorName, AssignedCreditDirectorNTName, AssignedDeptApprover1Name, AssignedDeptApprover1NTName, AssignedDeptApprover2Name, AssignedDeptApprover2NTName, AssignedDeptApprover3Name, AssignedDeptApprover3NTName, NULL AS AssignedDeptApprover4Name, NULL AS AssignedDeptApprover4NTName, AssignedDeptFPAReviewerName, AssignedDeptFPAReviewerNTName, NULL AS AssignedDivControllerName, AssignedDivControllerNTName, NULL AS AssignedDivOperationsName, AssignedDivOperationsNTName, NULL AS AssignedDivVPName, AssignedDivVPNTName, NULL AS AssignedEHSVPName, AssignedEHSVPNTName, NULL AS AssignedFPAReviewerName, AssignedFPAReviewerNTName, NULL AS AssignedITDirectorName, AssignedITDirectorNTName, NULL AS AssignedITReviewerApprovalStatus, NULL AS AssignedITReviewerAuthBy, NULL AS AssignedITReviewerAuthDate, NULL AS AssignedITReviewerComments, NULL AS AssignedITReviewerName, AssignedITReviewerNTName, AssignedOperationController, AssignedProjectManager, AssignedProjectManager2, AssignedProjectManager3, AssignedProjectManager4, AssignedProjectManager5, AssignedSalesManager, AssignedSiteManager, AssignedTechnicalManager, NULL AS AssignedTreasuryFinanceName, NULL AS AssignedTreasuryFinanceNTName, AssignedTreasuryReviewerNTName, NULL AS AssignedVPFinanceName, AssignedVPFinanceNTName, NULL AS AssignedVPSales, NULL AS AssignedVPTreasuryName, AssignedVPTreasuryNTName, NULL AS AssistantTreasurerApprovalStatus, NULL AS AssistantTreasurerAuthBy, NULL AS AssistantTreasurerAuthDate, NULL AS AssistantTreasurerComments, NULL AS AssumptionsExpectedBenefits, AuthorizedUser, NULL AS Background, BusinessUnitMessage, CEOApprovalStatus, CEOAuthBy, CEOAuthDate, CEOAuthLimit, CEOComments, CFOApprovalStatus, CFOAuthBy, CFOAuthDate, CFOAuthLimit, CFOComments, COOApprovalStatus, COOAuthBy, COOAuthDate, COOComments, CorporateAdministrator, CorporateCostCenter, CorporateDept, CreateDate, CreditDirectorApprovalStatus, CreditDirectorAuthBy, CreditDirectorAuthDate, CreditDirectorComments, CurrentLoginUser, NULL AS DepartmentApprover4Group, DeptApprover1ApprovalStatus, DeptApprover1AuthBy, DeptApprover1AuthDate, DeptApprover1Comments, DeptApprover2ApprovalStatus, DeptApprover2AuthBy, DeptApprover2AuthDate, DeptApprover2Comments, DeptApprover3ApprovalStatus, DeptApprover3AuthBy, DeptApprover3AuthDate, DeptApprover3Comments, NULL AS DeptApprover4ApprovalStatus, NULL AS DeptApprover4AuthBy, NULL AS DeptApprover4AuthDate, NULL AS DeptApprover4Comments, DeptFPAReviewerApprovalStatus, DeptFPAReviewerAuthBy, DeptFPAReviewerAuthDate, DeptFPAReviewerComments, DiscountRate, NULL AS DisplayName, DivControllerApprovalStatus, DivControllerAuthBy, DivControllerAuthDate, DivControllerComments, DivisionAdminNTName, NULL AS DivisionOnly, DivisionVPAuthLimit, DivisionVPComments, DivOperationsApprovalStatus, DivOperationsAuthBy, DivOperationsAuthDate, DivOperationsComments, DivVPApprovalStatus, DivVPAuthBy, DivVPAuthDate, EHSVPApprovalGroup, EHSVPApprovalStatus, EHSVPAuthBy, EHSVPAuthDate, EHSVPComments, EstimatedUsefulLife, FASTestPerformed, FPAReviewerApprovalStatus, FPAReviewerAuthBy, FPAReviewerAuthDate, FPAReviewerComments, group1, InternalRateofReturn, ITDirectorApprovalStatus, ITDirectorAuthBy, ITDirectorAuthDate, ITDirectorComments, ITReviewerApprovalStatus, ITReviewerAuthBy, ITReviewerAuthDate, ITReviewerComments, LeaseTerm, LeaseTermText, NULL AS li, Location, LocationMessage, MIRR, MIRRText, NeedEHSVPApproval, NeedResubmit, NeedsITWork, NetPresentValue, NetPresentValueRequired, NumOfAttach, OBAddCapacity, OBCostReduction, OBEnvironmental, OBMaintenanceRepair, OBOther, OBProductivity, OBQuality, OBRequired, OBRequiredTemp1, OBRequiredTemp2, OBRIMIImpulse, OBSafetyIndustrialRelations, OperationalBenefits, OperationControllerApprovalStatus, OperationControllerAuthBy, OperationControllerAuthDate, OperationControllerComments, OperationControllerNTName, PaybackPeriod, PlantDivDept, PreparedBy, NULL AS PresentCondition, ProjectDescription, ProjectManager2ApprovalStatus, ProjectManager2AuthBy, ProjectManager2AuthDate, ProjectManager2Comments, ProjectManager2NTName, ProjectManager3ApprovalStatus, ProjectManager3AuthBy, ProjectManager3AuthDate, ProjectManager3Comments, ProjectManager3NTName, ProjectManager4ApprovalStatus, ProjectManager4AuthBy, ProjectManager4AuthDate, ProjectManager4Comments, ProjectManager4NTName, ProjectManager5ApprovalStatus, ProjectManager5AuthBy, ProjectManager5AuthDate, ProjectManager5Comments, ProjectManager5NTName, ProjectManagerApprovalStatus, ProjectManagerAuthBy, ProjectManagerAuthDate, ProjectManagerComments, ProjectManagerNTName, ProjectName, ProjectStartDate, ProposedBegDate, ProposedCompletionDate, NULL AS ProposedCondition, NULL AS PurposeOfProject, QuestionAndAnswerGroup, ReqCapitalExpenditure, ReqCapitalizedInterest, ReqOperatingExpense, RequestID, RequestorEmail, RequestorNTName, RequestTotal, RequiredText, Revision, NULL AS RouteToCEO, RoutingStatus, SalesManagerApprovalReq, SalesManagerApprovalStatus, SalesManagerAuthBy, SalesManagerAuthDate, SalesManagerComments, SalesManagerNTName, NULL AS SalesManagerRequiredText, SaveForLater, SavingPerMonth, SiteManagerApprovalStatus, SiteManagerAuthBy, SiteManagerAuthDate, SiteManagerAuthLimit, SiteManagerComments, SiteManagerNTName, SubmissionDate, Submited, SubmitGroupChange, SubmitGroupNew, TechnicalManagerApprovalStatus, TechnicalManagerAuthBy, TechnicalManagerAuthDate, TechnicalManagerComments, TechnicalManagerNTName, TotalLeaseRevenues, TotalLeaseRevenuesgText, TotalProjectCost, NULL AS TreasuryFinanceApprovalStatus, NULL AS TreasuryFinanceAuthBy, NULL AS TreasuryFinanceAuthDate, NULL AS TreasuryFinanceComments, TreasuryReviewerApprovalStatus, TreasuryReviewerAuthBy, TreasuryReviewerAuthDate, TreasuryReviewerComments, TreasuryReviewerGroup, NULL AS ul, VPFinanceApprovalStatus, VPFinanceAuthBy, VPFinanceAuthDate, VPFinanceAuthLimit, VPFinanceComments, NULL AS VPSalesApprovalStatus, NULL AS VPSalesAuthBy, NULL AS VPSalesAuthDate, NULL AS VPSalesComments, NULL AS VPSalesNTName, NULL AS VPSalesRequiredText, VPTreasuryApprovalStatus, VPTreasuryAuthBy, VPTreasuryAuthDate, VPTreasuryComments FROM AR_Archive11 UNION ALL SELECT AccountId, AccountType, NULL AS AddtlCorpApproverApprovalStatus, NULL AS AddtlCorpApproverAuthBy, NULL AS AddtlCorpApproverAuthDate, NULL AS AddtlCorpApproverComments, NULL AS AddtlCorpApproverNTName, AnalysisOfAlternatives, ApprCapitalExpenditure, ApprCapitalizedInterest, ApprOperatingExpense, ApprovedBudgetTotal, ARNumber2, ARNumber3, ARNumberLen, ARNumberLocation, ARType, NULL AS AssignedAddtlCorpApprover, NULL AS AssignedAssistantTreasurerName, AssignedAssistantTreasurerNTName, AssignedCEODisplayName, AssignedCEONTName, NULL AS AssignedCFOName, AssignedCFONTName, AssignedCOONTName, NULL AS AssignedCreditDirectorName, AssignedCreditDirectorNTName, AssignedDeptApprover1Name, AssignedDeptApprover1NTName, AssignedDeptApprover2Name, AssignedDeptApprover2NTName, AssignedDeptApprover3Name, AssignedDeptApprover3NTName, NULL AS AssignedDeptApprover4Name, NULL AS AssignedDeptApprover4NTName, AssignedDeptFPAReviewerName, AssignedDeptFPAReviewerNTName, NULL AS AssignedDivControllerName, AssignedDivControllerNTName, NULL AS AssignedDivOperationsName, AssignedDivOperationsNTName, NULL AS AssignedDivVPName, AssignedDivVPNTName, NULL AS AssignedEHSVPName, AssignedEHSVPNTName, NULL AS AssignedFPAReviewerName, AssignedFPAReviewerNTName, NULL AS AssignedITDirectorName, AssignedITDirectorNTName, NULL AS AssignedITReviewerApprovalStatus, NULL AS AssignedITReviewerAuthBy, NULL AS AssignedITReviewerAuthDate, NULL AS AssignedITReviewerComments, NULL AS AssignedITReviewerName, AssignedITReviewerNTName, AssignedOperationController, AssignedProjectManager, AssignedProjectManager2, AssignedProjectManager3, AssignedProjectManager4, AssignedProjectManager5, AssignedSalesManager, AssignedSiteManager, AssignedTechnicalManager, NULL AS AssignedTreasuryFinanceName, AssignedTreasuryFinanceNTName, NULL AS AssignedTreasuryReviewerNTName, NULL AS AssignedVPFinanceName, AssignedVPFinanceNTName, AssignedVPSales, NULL AS AssignedVPTreasuryName, AssignedVPTreasuryNTName, AssistantTreasurerApprovalStatus, AssistantTreasurerAuthBy, AssistantTreasurerAuthDate, AssistantTreasurerComments, AssumptionsExpectedBenefits, AuthorizedUser, Background, BusinessUnitMessage, CEOApprovalStatus, CEOAuthBy, CEOAuthDate, CEOAuthLimit, CEOComments, CFOApprovalStatus, CFOAuthBy, CFOAuthDate, CFOAuthLimit, CFOComments, COOApprovalStatus, COOAuthBy, COOAuthDate, COOComments, CorporateAdministrator, CorporateCostCenter, CorporateDept, CreateDate, CreditDirectorApprovalStatus, CreditDirectorAuthBy, CreditDirectorAuthDate, CreditDirectorComments, CurrentLoginUser, NULL AS DepartmentApprover4Group, DeptApprover1ApprovalStatus, DeptApprover1AuthBy, DeptApprover1AuthDate, DeptApprover1Comments, DeptApprover2ApprovalStatus, DeptApprover2AuthBy, DeptApprover2AuthDate, DeptApprover2Comments, DeptApprover3ApprovalStatus, DeptApprover3AuthBy, DeptApprover3AuthDate, DeptApprover3Comments, NULL AS DeptApprover4ApprovalStatus, NULL AS DeptApprover4AuthBy, NULL AS DeptApprover4AuthDate, NULL AS DeptApprover4Comments, DeptFPAReviewerApprovalStatus, DeptFPAReviewerAuthBy, DeptFPAReviewerAuthDate, DeptFPAReviewerComments, DiscountRate, DisplayName, DivControllerApprovalStatus, DivControllerAuthBy, DivControllerAuthDate, DivControllerComments, DivisionAdminNTName, NULL AS DivisionOnly, DivisionVPAuthLimit, DivisionVPComments, DivOperationsApprovalStatus, DivOperationsAuthBy, DivOperationsAuthDate, DivOperationsComments, DivVPApprovalStatus, DivVPAuthBy, DivVPAuthDate, EHSVPApprovalGroup, EHSVPApprovalStatus, EHSVPAuthBy, EHSVPAuthDate, EHSVPComments, EstimatedUsefulLife, FASTestPerformed, FPAReviewerApprovalStatus, FPAReviewerAuthBy, FPAReviewerAuthDate, FPAReviewerComments, group1, InternalRateofReturn, ITDirectorApprovalStatus, ITDirectorAuthBy, ITDirectorAuthDate, ITDirectorComments, ITReviewerApprovalStatus, ITReviewerAuthBy, ITReviewerAuthDate, ITReviewerComments, LeaseTerm, LeaseTermText, NULL AS li, Location, LocationMessage, MIRR, MIRRText, NeedEHSVPApproval, NeedResubmit, NeedsITWork, NetPresentValue, NetPresentValueRequired, NumOfAttach, OBAddCapacity, OBCostReduction, OBEnvironmental, OBMaintenanceRepair, OBOther, OBProductivity, OBQuality, OBRequired, OBRequiredTemp1, OBRequiredTemp2, OBRIMIImpulse, OBSafetyIndustrialRelations, OperationalBenefits, OperationControllerApprovalStatus, OperationControllerAuthBy, OperationControllerAuthDate, OperationControllerComments, OperationControllerNTName, PaybackPeriod, PlantDivDept, PreparedBy, PresentCondition, ProjectDescription, ProjectManager2ApprovalStatus, ProjectManager2AuthBy, ProjectManager2AuthDate, ProjectManager2Comments, ProjectManager2NTName, ProjectManager3ApprovalStatus, ProjectManager3AuthBy, ProjectManager3AuthDate, ProjectManager3Comments, ProjectManager3NTName, ProjectManager4ApprovalStatus, ProjectManager4AuthBy, ProjectManager4AuthDate, ProjectManager4Comments, ProjectManager4NTName, ProjectManager5ApprovalStatus, ProjectManager5AuthBy, ProjectManager5AuthDate, ProjectManager5Comments, ProjectManager5NTName, ProjectManagerApprovalStatus, ProjectManagerAuthBy, ProjectManagerAuthDate, ProjectManagerComments, ProjectManagerNTName, ProjectName, ProjectStartDate, ProposedBegDate, ProposedCompletionDate, ProposedCondition, PurposeOfProject, QuestionAndAnswerGroup, ReqCapitalExpenditure, ReqCapitalizedInterest, ReqOperatingExpense, RequestID, RequestorEmail, RequestorNTName, RequestTotal, RequiredText, Revision, NULL AS RouteToCEO, RoutingStatus, SalesManagerApprovalReq, SalesManagerApprovalStatus, SalesManagerAuthBy, SalesManagerAuthDate, SalesManagerComments, SalesManagerNTName, SalesManagerRequiredText, SaveForLater, SavingPerMonth, SiteManagerApprovalStatus, SiteManagerAuthBy, SiteManagerAuthDate, SiteManagerAuthLimit, SiteManagerComments, SiteManagerNTName, SubmissionDate, Submited, SubmitGroupChange, SubmitGroupNew, TechnicalManagerApprovalStatus, TechnicalManagerAuthBy, TechnicalManagerAuthDate, TechnicalManagerComments, TechnicalManagerNTName, TotalLeaseRevenues, TotalLeaseRevenuesgText, TotalProjectCost, TreasuryFinanceApprovalStatus, TreasuryFinanceAuthBy, TreasuryFinanceAuthDate, TreasuryFinanceComments, NULL AS TreasuryReviewerApprovalStatus, NULL AS TreasuryReviewerAuthBy, NULL AS TreasuryReviewerAuthDate, NULL AS TreasuryReviewerComments, NULL AS TreasuryReviewerGroup, NULL AS ul, VPFinanceApprovalStatus, VPFinanceAuthBy, VPFinanceAuthDate, VPFinanceAuthLimit, VPFinanceComments, VPSalesApprovalStatus, VPSalesAuthBy, VPSalesAuthDate, VPSalesComments, VPSalesNTName, VPSalesRequiredText, VPTreasuryApprovalStatus, VPTreasuryAuthBy, VPTreasuryAuthDate, VPTreasuryComments FROM AR_Archive12 UNION ALL SELECT AccountId, AccountType, AddtlCorpApproverApprovalStatus, AddtlCorpApproverAuthBy, AddtlCorpApproverAuthDate, AddtlCorpApproverComments, AddtlCorpApproverNTName, AnalysisOfAlternatives, ApprCapitalExpenditure, ApprCapitalizedInterest, ApprOperatingExpense, ApprovedBudgetTotal, ARNumber2, ARNumber3, ARNumberLen, ARNumberLocation, ARType, AssignedAddtlCorpApprover, AssignedAssistantTreasurerName, AssignedAssistantTreasurerNTName, AssignedCEODisplayName, AssignedCEONTName, AssignedCFOName, AssignedCFONTName, AssignedCOONTName, AssignedCreditDirectorName, AssignedCreditDirectorNTName, AssignedDeptApprover1Name, AssignedDeptApprover1NTName, AssignedDeptApprover2Name, AssignedDeptApprover2NTName, AssignedDeptApprover3Name, AssignedDeptApprover3NTName, NULL AS AssignedDeptApprover4Name, NULL AS AssignedDeptApprover4NTName, AssignedDeptFPAReviewerName, AssignedDeptFPAReviewerNTName, AssignedDivControllerName, AssignedDivControllerNTName, AssignedDivOperationsName, AssignedDivOperationsNTName, AssignedDivVPName, AssignedDivVPNTName, AssignedEHSVPName, AssignedEHSVPNTName, AssignedFPAReviewerName, AssignedFPAReviewerNTName, AssignedITDirectorName, AssignedITDirectorNTName, NULL AS AssignedITReviewerApprovalStatus, NULL AS AssignedITReviewerAuthBy, NULL AS AssignedITReviewerAuthDate, NULL AS AssignedITReviewerComments, AssignedITReviewerName, AssignedITReviewerNTName, AssignedOperationController, AssignedProjectManager, AssignedProjectManager2, AssignedProjectManager3, AssignedProjectManager4, AssignedProjectManager5, AssignedSalesManager, AssignedSiteManager, AssignedTechnicalManager, AssignedTreasuryFinanceName, AssignedTreasuryFinanceNTName, NULL AS AssignedTreasuryReviewerNTName, AssignedVPFinanceName, AssignedVPFinanceNTName, AssignedVPSales, AssignedVPTreasuryName, AssignedVPTreasuryNTName, AssistantTreasurerApprovalStatus, AssistantTreasurerAuthBy, AssistantTreasurerAuthDate, AssistantTreasurerComments, AssumptionsExpectedBenefits, AuthorizedUser, Background, BusinessUnitMessage, CEOApprovalStatus, CEOAuthBy, CEOAuthDate, CEOAuthLimit, CEOComments, CFOApprovalStatus, CFOAuthBy, CFOAuthDate, CFOAuthLimit, CFOComments, COOApprovalStatus, COOAuthBy, COOAuthDate, COOComments, CorporateAdministrator, CorporateCostCenter, CorporateDept, CreateDate, CreditDirectorApprovalStatus, CreditDirectorAuthBy, CreditDirectorAuthDate, CreditDirectorComments, CurrentLoginUser, NULL AS DepartmentApprover4Group, DeptApprover1ApprovalStatus, DeptApprover1AuthBy, DeptApprover1AuthDate, DeptApprover1Comments, DeptApprover2ApprovalStatus, DeptApprover2AuthBy, DeptApprover2AuthDate, DeptApprover2Comments, DeptApprover3ApprovalStatus, DeptApprover3AuthBy, DeptApprover3AuthDate, DeptApprover3Comments, NULL AS DeptApprover4ApprovalStatus, NULL AS DeptApprover4AuthBy, NULL AS DeptApprover4AuthDate, NULL AS DeptApprover4Comments, DeptFPAReviewerApprovalStatus, DeptFPAReviewerAuthBy, DeptFPAReviewerAuthDate, DeptFPAReviewerComments, DiscountRate, DisplayName, DivControllerApprovalStatus, DivControllerAuthBy, DivControllerAuthDate, DivControllerComments, DivisionAdminNTName, NULL AS DivisionOnly, DivisionVPAuthLimit, DivisionVPComments, DivOperationsApprovalStatus, DivOperationsAuthBy, DivOperationsAuthDate, DivOperationsComments, DivVPApprovalStatus, DivVPAuthBy, DivVPAuthDate, EHSVPApprovalGroup, EHSVPApprovalStatus, EHSVPAuthBy, EHSVPAuthDate, EHSVPComments, EstimatedUsefulLife, FASTestPerformed, FPAReviewerApprovalStatus, FPAReviewerAuthBy, FPAReviewerAuthDate, FPAReviewerComments, group1, InternalRateofReturn, ITDirectorApprovalStatus, ITDirectorAuthBy, ITDirectorAuthDate, ITDirectorComments, ITReviewerApprovalStatus, ITReviewerAuthBy, ITReviewerAuthDate, ITReviewerComments, LeaseTerm, LeaseTermText, NULL AS li, Location, LocationMessage, MIRR, MIRRText, NeedEHSVPApproval, NeedResubmit, NeedsITWork, NetPresentValue, NetPresentValueRequired, NumOfAttach, OBAddCapacity, OBCostReduction, OBEnvironmental, OBMaintenanceRepair, OBOther, OBProductivity, OBQuality, OBRequired, OBRequiredTemp1, OBRequiredTemp2, OBRIMIImpulse, OBSafetyIndustrialRelations, OperationalBenefits, OperationControllerApprovalStatus, OperationControllerAuthBy, OperationControllerAuthDate, OperationControllerComments, OperationControllerNTName, PaybackPeriod, PlantDivDept, PreparedBy, PresentCondition, ProjectDescription, ProjectManager2ApprovalStatus, ProjectManager2AuthBy, ProjectManager2AuthDate, ProjectManager2Comments, ProjectManager2NTName, ProjectManager3ApprovalStatus, ProjectManager3AuthBy, ProjectManager3AuthDate, ProjectManager3Comments, ProjectManager3NTName, ProjectManager4ApprovalStatus, ProjectManager4AuthBy, ProjectManager4AuthDate, ProjectManager4Comments, ProjectManager4NTName, ProjectManager5ApprovalStatus, ProjectManager5AuthBy, ProjectManager5AuthDate, ProjectManager5Comments, ProjectManager5NTName, ProjectManagerApprovalStatus, ProjectManagerAuthBy, ProjectManagerAuthDate, ProjectManagerComments, ProjectManagerNTName, ProjectName, ProjectStartDate, ProposedBegDate, ProposedCompletionDate, ProposedCondition, PurposeOfProject, QuestionAndAnswerGroup, ReqCapitalExpenditure, ReqCapitalizedInterest, ReqOperatingExpense, RequestID, RequestorEmail, RequestorNTName, RequestTotal, RequiredText, Revision, RouteToCEO, RoutingStatus, SalesManagerApprovalReq, SalesManagerApprovalStatus, SalesManagerAuthBy, SalesManagerAuthDate, SalesManagerComments, SalesManagerNTName, SalesManagerRequiredText, SaveForLater, SavingPerMonth, SiteManagerApprovalStatus, SiteManagerAuthBy, SiteManagerAuthDate, SiteManagerAuthLimit, SiteManagerComments, SiteManagerNTName, SubmissionDate, Submited, SubmitGroupChange, SubmitGroupNew, TechnicalManagerApprovalStatus, TechnicalManagerAuthBy, TechnicalManagerAuthDate, TechnicalManagerComments, TechnicalManagerNTName, TotalLeaseRevenues, TotalLeaseRevenuesgText, TotalProjectCost, TreasuryFinanceApprovalStatus, TreasuryFinanceAuthBy, TreasuryFinanceAuthDate, TreasuryFinanceComments, NULL AS TreasuryReviewerApprovalStatus, NULL AS TreasuryReviewerAuthBy, NULL AS TreasuryReviewerAuthDate, NULL AS TreasuryReviewerComments, NULL AS TreasuryReviewerGroup, NULL AS ul, VPFinanceApprovalStatus, VPFinanceAuthBy, VPFinanceAuthDate, VPFinanceAuthLimit, VPFinanceComments, VPSalesApprovalStatus, VPSalesAuthBy, VPSalesAuthDate, VPSalesComments, VPSalesNTName, VPSalesRequiredText, VPTreasuryApprovalStatus, VPTreasuryAuthBy, VPTreasuryAuthDate, VPTreasuryComments FROM AR_Archive13 UNION ALL SELECT NULL AS AccountId, NULL AS AccountType, NULL AS AddtlCorpApproverApprovalStatus, NULL AS AddtlCorpApproverAuthBy, NULL AS AddtlCorpApproverAuthDate, NULL AS AddtlCorpApproverComments, NULL AS AddtlCorpApproverNTName, AnalysisOfAlternatives, ApprCapitalExpenditure, ApprCapitalizedInterest, ApprOperatingExpense, ApprovedBudgetTotal, ARNumber2, ARNumber3, ARNumberLen, ARNumberLocation, ARType, NULL AS AssignedAddtlCorpApprover, NULL AS AssignedAssistantTreasurerName, AssignedAssistantTreasurerNTName, AssignedCEODisplayName, AssignedCEONTName, NULL AS AssignedCFOName, AssignedCFONTName, AssignedCOONTName, NULL AS AssignedCreditDirectorName, AssignedCreditDirectorNTName, AssignedDeptApprover1Name, AssignedDeptApprover1NTName, AssignedDeptApprover2Name, AssignedDeptApprover2NTName, AssignedDeptApprover3Name, AssignedDeptApprover3NTName, NULL AS AssignedDeptApprover4Name, NULL AS AssignedDeptApprover4NTName, AssignedDeptFPAReviewerName, AssignedDeptFPAReviewerNTName, NULL AS AssignedDivControllerName, AssignedDivControllerNTName, NULL AS AssignedDivOperationsName, AssignedDivOperationsNTName, NULL AS AssignedDivVPName, AssignedDivVPNTName, NULL AS AssignedEHSVPName, AssignedEHSVPNTName, NULL AS AssignedFPAReviewerName, AssignedFPAReviewerNTName, NULL AS AssignedITDirectorName, AssignedITDirectorNTName, NULL AS AssignedITReviewerApprovalStatus, NULL AS AssignedITReviewerAuthBy, NULL AS AssignedITReviewerAuthDate, NULL AS AssignedITReviewerComments, NULL AS AssignedITReviewerName, AssignedITReviewerNTName, AssignedOperationController, AssignedProjectManager, AssignedProjectManager2, AssignedProjectManager3, AssignedProjectManager4, AssignedProjectManager5, AssignedSalesManager, AssignedSiteManager, AssignedTechnicalManager, NULL AS AssignedTreasuryFinanceName, AssignedTreasuryFinanceNTName, NULL AS AssignedTreasuryReviewerNTName, NULL AS AssignedVPFinanceName, AssignedVPFinanceNTName, NULL AS AssignedVPSales, NULL AS AssignedVPTreasuryName, AssignedVPTreasuryNTName, AssistantTreasurerApprovalStatus, AssistantTreasurerAuthBy, AssistantTreasurerAuthDate, AssistantTreasurerComments, AssumptionsExpectedBenefits, AuthorizedUser, Background, BusinessUnitMessage, CEOApprovalStatus, CEOAuthBy, CEOAuthDate, CEOAuthLimit, CEOComments, CFOApprovalStatus, CFOAuthBy, CFOAuthDate, CFOAuthLimit, CFOComments, COOApprovalStatus, COOAuthBy, COOAuthDate, COOComments, CorporateAdministrator, CorporateCostCenter, CorporateDept, CreateDate, CreditDirectorApprovalStatus, CreditDirectorAuthBy, CreditDirectorAuthDate, CreditDirectorComments, CurrentLoginUser, NULL AS DepartmentApprover4Group, DeptApprover1ApprovalStatus, DeptApprover1AuthBy, DeptApprover1AuthDate, DeptApprover1Comments, DeptApprover2ApprovalStatus, DeptApprover2AuthBy, DeptApprover2AuthDate, DeptApprover2Comments, DeptApprover3ApprovalStatus, DeptApprover3AuthBy, DeptApprover3AuthDate, DeptApprover3Comments, NULL AS DeptApprover4ApprovalStatus, NULL AS DeptApprover4AuthBy, NULL AS DeptApprover4AuthDate, NULL AS DeptApprover4Comments, DeptFPAReviewerAppr

Open in new window

It seems that its account for DeptFPAReviewerAppr partically here...but it also accounts for it in two other places....one as null and another as not null...seems off. I beleive the column its trying to do is [DeptFPAReviewerApprovalStatus]

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robb Hill

ASKER

I'd probably just loop thru each table and write each table's SELECT code separately to a SQL script table. Then the last thing in the does a "SELECT *" from that table. Each individual table would almost never exceed the max bytes of a SELECT line, so you'd see the whole thing together. Then you could verify the overall result set command looks then.

How would this auto create the alias like the first query I showed did.

(2) concatenate all the lines in the table into a single [n]varchar(max) variable and EXEC it.

Not sure here...wouldnt this give me dup collumns?

Scott Pletcher

I don't know. I didn't see any aliases in the code you posted and I have no DDL or any other way to know what the tables you are working with look like.

My suggestion was to loop thru the tables, and for each table to add its generated code to a separate SQL table. For example:

DECLARE @first_time_thru bit
DECLARE @sql nvarchar(max)
DECLARE @table_name nvarchar(128)

CREATE TABLE #sql (
id int IDENTITY(1, 1) PRIMARY KEY,
sql nnvarchar(max) NULL
)

DECLARE cursor_tables CURSOR LOCAL STATIC FOR
SELECT name AS table_name
FROM sys.tables
WHERE name LIKE 'AR%'

OPEN cursor_tables
SET @first_time_thru = 1

WHILE 1 = 1
FETCH NEXT FROM cursor_tables INTO @table_name
IF @@FETCH_STATUS <> 0
BREAK
IF @first_time_thru = 1
SET @first_time_thru = 0
ELSE
INSERT INTO #sql ( sql ) VALUES('UNION')

--the sql that generates column list, with NULL columns as needed, for the current table
SELECT @sql = STUFF((SELECT
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)
...
ORDER BY ...

INSERT INTO #sql ( sql )
SELECT /* '...' + */ @sql /* + '...' */

END --WHILE

DEALLOCATE cursor_tables

--basic SELECT, or concatenate all the lines in #sql into @sql in order and then EXEC(@sql)
SELECT *
FROM #sql
ORDER BY id

Robb Hill

ASKER

--the sql that generates column list, with NULL columns as needed, for the current table
SELECT @sql = STUFF((SELECT
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)

How would this be for the current table? Sorry I am trying to follow these assumptions.

For example here is two of the 18 tables if this helps.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [ApprCapitalExpenditure]
      ,[ApprCapitalizedInterest]
      ,[ApprOperatingExpense]
      ,[ApprovedBudgetTotal]
      ,[ARNumber2]
      ,[ARNumber3]
      ,[ARNumberLen]
      ,[ARNumberLocation]
      ,[AssignedCEONTName]
      ,[AssignedCFONTName]
      ,[AssignedCOONTName]
      ,[AssignedCreditDirectorNTName]
      ,[AssignedDivControllerNTName]
      ,[AssignedDivOperationsNTName]
      ,[AssignedDivVPNTName]
      ,[AssignedEHSVPNTName]
      ,[AssignedFPAReviewerNTName]
      ,[AssignedITDirectorNTName]
      ,[AssignedOperationController]
      ,[AssignedProjectManager]
      ,[AssignedSalesManager]
      ,[AssignedSiteManager]
      ,[AssignedTechnicalManager]
      ,[AssignedTreasuryReviewerNTName]
      ,[AssignedVPFinanceNTName]
      ,[AssignedVPTreasuryNTName]
      ,[BusinessUnitMessage]
      ,[CEOApprovalStatus]
      ,[CEOAuthBy]
      ,[CEOAuthDate]
      ,[CEOAuthLimit]
      ,[CEOComments]
      ,[CFOApprovalStatus]
      ,[CFOAuthBy]
      ,[CFOAuthDate]
      ,[CFOAuthLimit]
      ,[CFOComments]
      ,[COOApprovalStatus]
      ,[COOAuthBy]
      ,[COOAuthDate]
      ,[COOComments]
      ,[CorporateDept]
      ,[CreateDate]
      ,[CreditDirectorApprovalStatus]
      ,[CreditDirectorAuthBy]
      ,[CreditDirectorAuthDate]
      ,[CreditDirectorComments]
      ,[DiscountRate]
      ,[DivControllerApprovalStatus]
      ,[DivControllerAuthBy]
      ,[DivControllerAuthDate]
      ,[DivControllerComments]
      ,[DivisionVPAuthLimit]
      ,[DivisionVPComments]
      ,[DivOperationsApprovalStatus]
      ,[DivOperationsAuthBy]
      ,[DivOperationsAuthDate]
      ,[DivOperationsComments]
      ,[DivVPApprovalStatus]
      ,[DivVPAuthBy]
      ,[DivVPAuthDate]
      ,[EHSVPApprovalGroup]
      ,[EHSVPApprovalStatus]
      ,[EHSVPAuthBy]
      ,[EHSVPAuthDate]
      ,[EHSVPComments]
      ,[EstimatedUsefulLife]
      ,[FASTestPerformed]
      ,[FPAReviewerApprovalStatus]
      ,[FPAReviewerAuthBy]
      ,[FPAReviewerAuthDate]
      ,[FPAReviewerComments]
      ,[group1]
      ,[InternalRateofReturn]
      ,[ITDirectorApprovalStatus]
      ,[ITDirectorAuthBy]
      ,[ITDirectorAuthDate]
      ,[ITDirectorComments]
      ,[LeaseTerm]
      ,[LeaseTermText]
      ,[Location]
      ,[LocationMessage]
      ,[MIRR]
      ,[MIRRText]
      ,[NeedEHSVPApproval]
      ,[NeedResubmit]
      ,[NeedsITWork]
      ,[NetPresentValue]
      ,[NetPresentValueRequired]
      ,[NumOfAttach]
      ,[OBAddCapacity]
      ,[OBCostReduction]
      ,[OBEnvironmental]
      ,[OBMaintenanceRepair]
      ,[OBProductivity]
      ,[OBQuality]
      ,[OBRequired]
      ,[OBRequiredTemp1]
      ,[OBRequiredTemp2]
      ,[OBRIMIImpulse]
      ,[OBSafetyIndustrialRelations]
      ,[OperationalBenefits]
      ,[OperationControllerApprovalStatus]
      ,[OperationControllerAuthBy]
      ,[OperationControllerAuthDate]
      ,[OperationControllerComments]
      ,[OperationControllerNTName]
      ,[PaybackPeriod]
      ,[PlantDivDept]
      ,[PreparedBy]
      ,[ProjectManagerApprovalStatus]
      ,[ProjectManagerAuthBy]
      ,[ProjectManagerAuthDate]
      ,[ProjectManagerComments]
      ,[ProjectManagerNTName]
      ,[ProjectName]
      ,[ProjectStartDate]
      ,[ProposedBegDate]
      ,[ProposedCompletionDate]
      ,[QuestionAndAnswerGroup]
      ,[ReqCapitalExpenditure]
      ,[ReqCapitalizedInterest]
      ,[ReqOperatingExpense]
      ,[RequestID]
      ,[RequestorEmail]
      ,[RequestorNTName]
      ,[RequestTotal]
      ,[RequiredText]
      ,[Revision]
      ,[RoutingStatus]
      ,[SalesManagerApprovalReq]
      ,[SalesManagerApprovalStatus]
      ,[SalesManagerAuthBy]
      ,[SalesManagerAuthDate]
      ,[SalesManagerComments]
      ,[SalesManagerNTName]
      ,[SaveForLater]
      ,[SavingPerMonth]
      ,[SiteManagerApprovalStatus]
      ,[SiteManagerAuthBy]
      ,[SiteManagerAuthDate]
      ,[SiteManagerAuthLimit]
      ,[SiteManagerComments]
      ,[SiteManagerNTName]
      ,[SubmissionDate]
      ,[Submited]
      ,[SubmitGroupChange]
      ,[SubmitGroupNew]
      ,[TechnicalManagerApprovalStatus]
      ,[TechnicalManagerAuthBy]
      ,[TechnicalManagerAuthDate]
      ,[TechnicalManagerComments]
      ,[TechnicalManagerNTName]
      ,[TotalLeaseRevenues]
      ,[TotalLeaseRevenuesgText]
      ,[TotalProjectCost]
      ,[TreasuryReviewerApprovalStatus]
      ,[TreasuryReviewerAuthBy]
      ,[TreasuryReviewerAuthDate]
      ,[TreasuryReviewerComments]
      ,[TreasuryReviewerGroup]
      ,[VPFinanceApprovalStatus]
      ,[VPFinanceAuthBy]
      ,[VPFinanceAuthDate]
      ,[VPFinanceAuthLimit]
      ,[VPFinanceComments]
      ,[VPTreasuryApprovalStatus]
      ,[VPTreasuryAuthBy]
      ,[VPTreasuryAuthDate]
      ,[VPTreasuryComments]
      ,[ProjectDescription]
      ,[OBOther]
  FROM [SharePoint2007Archive].[dbo].[AR_Archive1]

Open in new window

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [AccountId]
      ,[AccountType]
      ,[AnalysisOfAlternatives]
      ,[ApprCapitalExpenditure]
      ,[ApprCapitalizedInterest]
      ,[ApprOperatingExpense]
      ,[ApprovedBudgetTotal]
      ,[ARNumber2]
      ,[ARNumber3]
      ,[ARNumberLen]
      ,[ARNumberLocation]
      ,[ARType]
      ,[AssignedAssistantTreasurerNTName]
      ,[AssignedCEODisplayName]
      ,[AssignedCEONTName]
      ,[AssignedCFONTName]
      ,[AssignedCOONTName]
      ,[AssignedCreditDirectorNTName]
      ,[AssignedDeptApprover1Name]
      ,[AssignedDeptApprover1NTName]
      ,[AssignedDeptApprover2Name]
      ,[AssignedDeptApprover2NTName]
      ,[AssignedDeptApprover3Name]
      ,[AssignedDeptApprover3NTName]
      ,[AssignedDeptFPAReviewerName]
      ,[AssignedDeptFPAReviewerNTName]
      ,[AssignedDivControllerNTName]
      ,[AssignedDivOperationsNTName]
      ,[AssignedDivVPNTName]
      ,[AssignedEHSVPNTName]
      ,[AssignedFPAReviewerNTName]
      ,[AssignedITDirectorNTName]
      ,[AssignedITReviewerNTName]
      ,[AssignedOperationController]
      ,[AssignedProjectManager]
      ,[AssignedProjectManager2]
      ,[AssignedProjectManager3]
      ,[AssignedProjectManager4]
      ,[AssignedProjectManager5]
      ,[AssignedSalesManager]
      ,[AssignedSiteManager]
      ,[AssignedTechnicalManager]
      ,[AssignedTreasuryFinanceNTName]
      ,[AssignedVPFinanceNTName]
      ,[AssignedVPSales]
      ,[AssignedVPTreasuryNTName]
      ,[AssistantTreasurerApprovalStatus]
      ,[AssistantTreasurerAuthBy]
      ,[AssistantTreasurerAuthDate]
      ,[AssistantTreasurerComments]
      ,[AssumptionsExpectedBenefits]
      ,[AuthorizedUser]
      ,[Background]
      ,[BusinessUnitMessage]
      ,[CEOApprovalStatus]
      ,[CEOAuthBy]
      ,[CEOAuthDate]
      ,[CEOAuthLimit]
      ,[CEOComments]
      ,[CFOApprovalStatus]
      ,[CFOAuthBy]
      ,[CFOAuthDate]
      ,[CFOAuthLimit]
      ,[CFOComments]
      ,[COOApprovalStatus]
      ,[COOAuthBy]
      ,[COOAuthDate]
      ,[COOComments]
      ,[CorporateAdministrator]
      ,[CorporateCostCenter]
      ,[CorporateDept]
      ,[CreateDate]
      ,[CreditDirectorApprovalStatus]
      ,[CreditDirectorAuthBy]
      ,[CreditDirectorAuthDate]
      ,[CreditDirectorComments]
      ,[CurrentLoginUser]
      ,[DeptApprover1ApprovalStatus]
      ,[DeptApprover1AuthBy]
      ,[DeptApprover1AuthDate]
      ,[DeptApprover1Comments]
      ,[DeptApprover2ApprovalStatus]
      ,[DeptApprover2AuthBy]
      ,[DeptApprover2AuthDate]
      ,[DeptApprover2Comments]
      ,[DeptApprover3ApprovalStatus]
      ,[DeptApprover3AuthBy]
      ,[DeptApprover3AuthDate]
      ,[DeptApprover3Comments]
      ,[DeptFPAReviewerApprovalStatus]
      ,[DeptFPAReviewerAuthBy]
      ,[DeptFPAReviewerAuthDate]
      ,[DeptFPAReviewerComments]
      ,[DiscountRate]
      ,[DisplayName]
      ,[DivControllerApprovalStatus]
      ,[DivControllerAuthBy]
      ,[DivControllerAuthDate]
      ,[DivControllerComments]
      ,[DivisionAdminNTName]
      ,[DivisionVPAuthLimit]
      ,[DivisionVPComments]
      ,[DivOperationsApprovalStatus]
      ,[DivOperationsAuthBy]
      ,[DivOperationsAuthDate]
      ,[DivOperationsComments]
      ,[DivVPApprovalStatus]
      ,[DivVPAuthBy]
      ,[DivVPAuthDate]
      ,[EHSVPApprovalGroup]
      ,[EHSVPApprovalStatus]
      ,[EHSVPAuthBy]
      ,[EHSVPAuthDate]
      ,[EHSVPComments]
      ,[EstimatedUsefulLife]
      ,[FASTestPerformed]
      ,[FPAReviewerApprovalStatus]
      ,[FPAReviewerAuthBy]
      ,[FPAReviewerAuthDate]
      ,[FPAReviewerComments]
      ,[group1]
      ,[InternalRateofReturn]
      ,[ITDirectorApprovalStatus]
      ,[ITDirectorAuthBy]
      ,[ITDirectorAuthDate]
      ,[ITDirectorComments]
      ,[ITReviewerApprovalStatus]
      ,[ITReviewerAuthBy]
      ,[ITReviewerAuthDate]
      ,[ITReviewerComments]
      ,[LeaseTerm]
      ,[LeaseTermText]
      ,[Location]
      ,[LocationMessage]
      ,[MIRR]
      ,[MIRRText]
      ,[NeedEHSVPApproval]
      ,[NeedResubmit]
      ,[NeedsITWork]
      ,[NetPresentValue]
      ,[NetPresentValueRequired]
      ,[NumOfAttach]
      ,[OBAddCapacity]
      ,[OBCostReduction]
      ,[OBEnvironmental]
      ,[OBMaintenanceRepair]
      ,[OBProductivity]
      ,[OBQuality]
      ,[OBRequired]
      ,[OBRequiredTemp1]
      ,[OBRequiredTemp2]
      ,[OBRIMIImpulse]
      ,[OBSafetyIndustrialRelations]
      ,[OperationalBenefits]
      ,[OperationControllerApprovalStatus]
      ,[OperationControllerAuthBy]
      ,[OperationControllerAuthDate]
      ,[OperationControllerComments]
      ,[OperationControllerNTName]
      ,[PaybackPeriod]
      ,[PlantDivDept]
      ,[PreparedBy]
      ,[PresentCondition]
      ,[ProjectManager2ApprovalStatus]
      ,[ProjectManager2AuthBy]
      ,[ProjectManager2AuthDate]
      ,[ProjectManager2Comments]
      ,[ProjectManager2NTName]
      ,[ProjectManager3ApprovalStatus]
      ,[ProjectManager3AuthBy]
      ,[ProjectManager3AuthDate]
      ,[ProjectManager3Comments]
      ,[ProjectManager3NTName]
      ,[ProjectManager4ApprovalStatus]
      ,[ProjectManager4AuthBy]
      ,[ProjectManager4AuthDate]
      ,[ProjectManager4Comments]
      ,[ProjectManager4NTName]
      ,[ProjectManager5ApprovalStatus]
      ,[ProjectManager5AuthBy]
      ,[ProjectManager5AuthDate]
      ,[ProjectManager5Comments]
      ,[ProjectManager5NTName]
      ,[ProjectManagerApprovalStatus]
      ,[ProjectManagerAuthBy]
      ,[ProjectManagerAuthDate]
      ,[ProjectManagerComments]
      ,[ProjectManagerNTName]
      ,[ProjectName]
      ,[ProjectStartDate]
      ,[ProposedBegDate]
      ,[ProposedCompletionDate]
      ,[ProposedCondition]
      ,[PurposeOfProject]
      ,[QuestionAndAnswerGroup]
      ,[ReqCapitalExpenditure]
      ,[ReqCapitalizedInterest]
      ,[ReqOperatingExpense]
      ,[RequestID]
      ,[RequestorEmail]
      ,[RequestorNTName]
      ,[RequestTotal]
      ,[RequiredText]
      ,[Revision]
      ,[RouteToCEO]
      ,[RoutingStatus]
      ,[SalesManagerApprovalReq]
      ,[SalesManagerApprovalStatus]
      ,[SalesManagerAuthBy]
      ,[SalesManagerAuthDate]
      ,[SalesManagerComments]
      ,[SalesManagerNTName]
      ,[SalesManagerRequiredText]
      ,[SaveForLater]
      ,[SavingPerMonth]
      ,[SiteManagerApprovalStatus]
      ,[SiteManagerAuthBy]
      ,[SiteManagerAuthDate]
      ,[SiteManagerAuthLimit]
      ,[SiteManagerComments]
      ,[SiteManagerNTName]
      ,[SubmissionDate]
      ,[Submited]
      ,[SubmitGroupChange]
      ,[SubmitGroupNew]
      ,[TechnicalManagerApprovalStatus]
      ,[TechnicalManagerAuthBy]
      ,[TechnicalManagerAuthDate]
      ,[TechnicalManagerComments]
      ,[TechnicalManagerNTName]
      ,[TotalLeaseRevenues]
      ,[TotalLeaseRevenuesgText]
      ,[TotalProjectCost]
      ,[TreasuryFinanceApprovalStatus]
      ,[TreasuryFinanceAuthBy]
      ,[TreasuryFinanceAuthDate]
      ,[TreasuryFinanceComments]
      ,[VPFinanceApprovalStatus]
      ,[VPFinanceAuthBy]
      ,[VPFinanceAuthDate]
      ,[VPFinanceAuthLimit]
      ,[VPFinanceComments]
      ,[VPSalesApprovalStatus]
      ,[VPSalesAuthBy]
      ,[VPSalesAuthDate]
      ,[VPSalesComments]
      ,[VPSalesNTName]
      ,[VPSalesRequiredText]
      ,[VPTreasuryApprovalStatus]
      ,[VPTreasuryAuthBy]
      ,[VPTreasuryAuthDate]
      ,[VPTreasuryComments]
      ,[ProjectDescription]
      ,[OBOther]
  FROM [SharePoint2007Archive].[dbo].[AR_Archive9]

Open in new window

Scott Pletcher

As in your original SQL, just replace the original WHERE for all table with a WHERE for a single table:

;WITH
AllTables (TABLE_NAME) AS (
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @table_name
),
...

Whatever you're using to generate the SQL above, except for one table at a time. That also makes it easier later to customize the output if that's ever needed.

Robb Hill

ASKER

Sorry I dont follow. This would iterate table by table creating one line for the sql. Its not doing any compare against other sql to inject any null columns on unmatched.

Am I missing something.

Also any idea how to get this statement into a temp table? I am having issues due to with

WITH 
    AllTables (TABLE_NAME) AS (
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME LIKE 'AR%'
    ),
    TablesWithSelectors (TABLE_NAME, COLUMN_NAME, Selector) AS (
        SELECT t.TABLE_NAME, a.COLUMN_NAME, CASE WHEN b.COLUMN_NAME IS NULL THEN 'NULL AS ' ELSE '' END + a.COLUMN_NAME
        FROM AllTables t
        CROSS JOIN (SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM AllTables)) a
        LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS b ON b.TABLE_NAME = t.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME
    ),
    SelectStatements (Sql) AS (
        SELECT
            'SELECT ' +
            STUFF((
                SELECT ', ' + Selector
                FROM TablesWithSelectors
                WHERE TABLE_NAME = r.TABLE_NAME
                FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
            , 1, 2, '') +
            ' FROM ' +
            TABLE_NAME
        FROM TablesWithSelectors r
        GROUP BY TABLE_NAME
    )
SELECT STUFF((        
		SELECT  ' UNION ALL ' + sql		
        FROM SelectStatements
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'), 1, 11, '')

Open in new window

Scott Pletcher

DECLARE @sql varchar(max);

--as part of the loop process:

WITH
AllTables (TABLE_NAME) AS (
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @table_name
),
TablesWithSelectors (TABLE_NAME, COLUMN_NAME, Selector) AS (
SELECT t.TABLE_NAME, a.COLUMN_NAME, CASE WHEN b.COLUMN_NAME IS NULL THEN 'NULL AS ' ELSE '' END + a.COLUMN_NAME
FROM AllTables t
CROSS JOIN (SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM AllTables)) a
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS b ON b.TABLE_NAME = t.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME
),
SelectStatements (Sql) AS (
SELECT
'SELECT ' +
STUFF((
SELECT ', ' + Selector
FROM TablesWithSelectors
WHERE TABLE_NAME = r.TABLE_NAME
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
, 1, 2, '') +
' FROM ' +
TABLE_NAME
FROM TablesWithSelectors r
GROUP BY TABLE_NAME
)
SELECT @sql = STUFF((
SELECT sql
FROM SelectStatements
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'), 1, 11, '')

IF @first_time_thru = 1
SET @first_time_thru = 0
ELSE
INSERT INTO #sql VALUES('UNION ALL')

INSERT INTO #sql VALUES(@sql)

Robb Hill

ASKER

OK I am getting Deallocate expecting Conversation in the intelisense.

If I try and run I get the following:

Msg 135, Level 15, State 1, Line 21
Cannot use a BREAK statement outside the scope of a WHILE statement.
Msg 319, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 38
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 68
Incorrect syntax near the keyword 'DEALLOCATE'.

Open in new window

The sql looks like this as of now:

DECLARE @first_time_thru bit
DECLARE @sql nvarchar(max)
DECLARE @table_name nvarchar(128)

CREATE TABLE #sql (
    id int IDENTITY(1, 1) PRIMARY KEY,
    sql nvarchar(max) NULL
    )

DECLARE cursor_tables CURSOR LOCAL STATIC FOR
SELECT name AS table_name
FROM sys.tables
WHERE name LIKE 'AR%'

OPEN cursor_tables
SET @first_time_thru = 1

WHILE 1 = 1
FETCH NEXT FROM cursor_tables INTO @table_name
IF @@FETCH_STATUS <> 0
    BREAK
IF @first_time_thru = 1
    SET @first_time_thru = 0
ELSE
    INSERT INTO #sql ( sql ) VALUES('UNION')

WITH 
    AllTables (TABLE_NAME) AS (
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = @table_name
    ),
    TablesWithSelectors (TABLE_NAME, COLUMN_NAME, Selector) AS (
        SELECT t.TABLE_NAME, a.COLUMN_NAME, CASE WHEN b.COLUMN_NAME IS NULL THEN 'NULL AS ' ELSE '' END + a.COLUMN_NAME
        FROM AllTables t
        CROSS JOIN (SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM AllTables)) a
        LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS b ON b.TABLE_NAME = t.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME
    ),
    SelectStatements (Sql) AS (
        SELECT
            'SELECT ' +
            STUFF((
                SELECT ', ' + Selector
                FROM TablesWithSelectors
                WHERE TABLE_NAME = r.TABLE_NAME
                FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
            , 1, 2, '') +
            ' FROM ' +
            TABLE_NAME
        FROM TablesWithSelectors r
        GROUP BY TABLE_NAME
    )
SELECT @sql = STUFF((        
            SELECT  sql            
        FROM SelectStatements
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'), 1, 11, '')

IF @first_time_thru = 1
    SET @first_time_thru = 0
ELSE
    INSERT INTO #sql VALUES('UNION ALL')

INSERT INTO #sql VALUES(@sql)


END --WHILE

DEALLOCATE cursor_tables

--basic SELECT, or concatenate all the lines in #sql into @sql in order and then EXEC(@sql)
SELECT *
FROM #sql
ORDER BY id

Open in new window

Scott Pletcher

Sorry, I left out the BEGIN after the WHILE. And I left a ; off one INSERT statement:

DECLARE @first_time_thru bit
DECLARE @sql nvarchar(max)
DECLARE @table_name nvarchar(128)

CREATE TABLE #sql (
id int IDENTITY(1, 1) PRIMARY KEY,
sql nvarchar(max) NULL
)

DECLARE cursor_tables CURSOR LOCAL STATIC FOR
SELECT name AS table_name
FROM sys.tables
WHERE name LIKE 'AR%'

OPEN cursor_tables
SET @first_time_thru = 1

WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_tables INTO @table_name
IF @@FETCH_STATUS <> 0
BREAK
IF @first_time_thru = 1
SET @first_time_thru = 0
ELSE
INSERT INTO #sql ( sql ) VALUES('UNION');

;WITH
AllTables (TABLE_NAME) AS (
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @table_name
),
TablesWithSelectors (TABLE_NAME, COLUMN_NAME, Selector) AS (
SELECT t.TABLE_NAME, a.COLUMN_NAME, CASE WHEN b.COLUMN_NAME IS NULL THEN 'NULL AS ' ELSE '' END + a.COLUMN_NAME
FROM AllTables t
CROSS JOIN (SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM AllTables)) a
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS b ON b.TABLE_NAME = t.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME
),
SelectStatements (Sql) AS (
SELECT
'SELECT ' +
STUFF((
SELECT ', ' + Selector
FROM TablesWithSelectors
WHERE TABLE_NAME = r.TABLE_NAME
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
, 1, 2, '') +
' FROM ' +
TABLE_NAME
FROM TablesWithSelectors r
GROUP BY TABLE_NAME
)
SELECT @sql = STUFF((
SELECT sql
FROM SelectStatements
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'), 1, 11, '')

IF @first_time_thru = 1
SET @first_time_thru = 0
ELSE
INSERT INTO #sql VALUES('UNION ALL')

INSERT INTO #sql VALUES(@sql)

END --WHILE

DEALLOCATE cursor_tables

--basic SELECT, or concatenate all the lines in #sql into @sql in order and then EXEC(@sql)
SELECT *
FROM #sql
ORDER BY id

Robb Hill

ASKER

ok..hmmm...im thinking this was not desired result:)

sql
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL
UNION
UNION ALL
NULL

Open in new window

Scott Pletcher

Nope. You'll need to verify the logic, I'm just trying to give broad pointers, not going thru all the code line by line, although I did to make the corrections below.

DECLARE @first_time_thru bit
DECLARE @sql nvarchar(max)
DECLARE @table_name nvarchar(128)

CREATE TABLE #sql (
id int IDENTITY(1, 1) PRIMARY KEY,
sql nvarchar(max) NULL
)

DECLARE cursor_tables CURSOR LOCAL STATIC FOR
SELECT name AS table_name
FROM sys.tables
WHERE name LIKE 'AR%'

OPEN cursor_tables
SET @first_time_thru = 1

WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_tables INTO @table_name
IF @@FETCH_STATUS <> 0
BREAK
IF @first_time_thru = 1
SET @first_time_thru = 0
ELSE
INSERT INTO #sql ( sql ) VALUES('UNION');

;WITH
AllTables (TABLE_NAME) AS (
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'AR%'
),
TablesWithSelectors (TABLE_NAME, COLUMN_NAME, Selector) AS (
SELECT t.TABLE_NAME, a.COLUMN_NAME, CASE WHEN b.COLUMN_NAME IS NULL THEN 'NULL AS ' ELSE '' END + a.COLUMN_NAME
FROM AllTables t
CROSS JOIN (SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM AllTables)) a
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS b ON b.TABLE_NAME = t.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME
),
SelectStatements (Sql) AS (
SELECT
'SELECT ' +
STUFF((
SELECT ', ' + Selector
FROM TablesWithSelectors
WHERE TABLE_NAME = r.TABLE_NAME
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
, 1, 2, '') +
' FROM ' +
TABLE_NAME
FROM TablesWithSelectors r
WHERE r.TABLE_NAME = @table_name
)
SELECT @sql = STUFF((
SELECT sql
FROM SelectStatements
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'), 1, 11, '')

IF @first_time_thru = 1
SET @first_time_thru = 0
ELSE
INSERT INTO #sql VALUES('UNION ALL')

INSERT INTO #sql VALUES(@sql)

END --WHILE

Robb Hill

ASKER

not sure how to use this..

Your change

GROUP BY TABLE_NAME

to
WHERE r.TABLE_NAME = @table_name

yields null versus the sql statement that is created i the sql above it.

What is trying to be accomplished here. Not sure how to refactor that.

Scott Pletcher

I did post a final version of the code where I took out the GROUP BY.

The idea is to generate a SELECT statement separately for each table rather than in one giant statement. That's all. You can go back to the all-in-one if you prefer. But you'll have trouble displaying it, since SSMS won't output that many chars in one column, it'll get cut off like before.

Robb Hill

ASKER

yes that version returns null...not a sql statement when I do a select from the table it inserts to.

Robb Hill

ASKER

it looks like the orginal statement i posted worked all along...it was just I had to save to a file to get the output....

Sorry for wasting any of your time. I will give you credit for all the effort.

Thanks