locked
Need Help with DTS RRS feed

  • Question

  • I have a query and i need to do a join to a table that needs to be created using DTS.

    I have a list of items in excel . The query i have list all the items in the database. I simply want to create a table for the items in my excel book and do a join and get the results.  Can anyone help me with this ?

    SELECT a.location_id, a.inv_mast_uid, a.item_id,  
    sum(a.inv_period_usage) as Actual_Usage_1YR,
    sum(a.number_of_orders) as No_Of_Orders,
    sum(a.filtered_usage) as filtered_usage
    FROM dbo.p21_view_inv_period_usage a  
    INNER JOIN dbo.p21_view_demand_period b ON  
    a.demand_period_uid = b.demand_period_uid   
    WHERE   
    convert(varchar(4),year_for_period) + 
    case when period <10 then '0' + convert(varchar(2),period) 
    else convert(varchar(2),period) 
    end 
    between  
    convert(varchar(4),year(getdate())-1)+ 
    case when convert(varchar(4),month(getdate()))<10 then 
    '0' + convert(varchar(4),month(getdate())) 
    else convert(varchar(4),month(getdate())) 
    end 
    AND 
    convert(varchar(4),year(getdate()))+ 
    case when convert(varchar(4),month(getdate()))<10 then 
    '0' + convert(varchar(4),month(getdate())) 
    else convert(varchar(4),month(getdate())) 
    end-1
    AND
    a.location_id=10052
    GROUP BY a.location_id, a.inv_mast_uid, a.item_id   
    FM
    Monday, December 15, 2008 9:00 PM

Answers

  • You can either join the excel data directly onto the existing query or insert the excel data into a table and join that data onto the existing query.  Either way you will have to add another inner join and use the correct on clause to join the data.

    SELECT a.location_id, a.inv_mast_uid, a.item_id,     
    sum(a.inv_period_usage) as Actual_Usage_1YR,   
    sum(a.number_of_orders) as No_Of_Orders,  
    sum(a.filtered_usage) as filtered_usage  
    FROM dbo.p21_view_inv_period_usage a     
    INNER JOIN dbo.p21_view_demand_period b ON     
    a.demand_period_uid = b.demand_period_uid   
    INNER JOIN(  
        SELECT *  
        OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
        'Excel 8.0;Database=C:\Documents and Settings\ahaines\Desktop\test.xls', [sheet1$])  
    as Excel  
        ON Excel.MyColumn = a.MyColumn 
    WHERE      
    convert(varchar(4),year_for_period) +    
    case when period <10 then '0' + convert(varchar(2),period)    
    else convert(varchar(2),period)    
    end    
    between     
    convert(varchar(4),year(getdate())-1)+    
    case when convert(varchar(4),month(getdate()))<10 then    
    '0' + convert(varchar(4),month(getdate()))    
    else convert(varchar(4),month(getdate()))    
    end    
    AND    
    convert(varchar(4),year(getdate()))+    
    case when convert(varchar(4),month(getdate()))<10 then    
    '0' + convert(varchar(4),month(getdate()))    
    else convert(varchar(4),month(getdate()))    
    end-1  
    AND 
    a.location_id=10052  
    GROUP BY a.location_id, a.inv_mast_uid, a.item_id    

    OR

    SELECT a.location_id, a.inv_mast_uid, a.item_id,        
    sum(a.inv_period_usage) as Actual_Usage_1YR,      
    sum(a.number_of_orders) as No_Of_Orders,     
    sum(a.filtered_usage) as filtered_usage     
    FROM dbo.p21_view_inv_period_usage a        
    INNER JOIN dbo.p21_view_demand_period b ON        
    a.demand_period_uid = b.demand_period_uid     
    INNER JOIN readonly.uitems as Excel     
        ON Excel.MyColumn = a.MyColumn    
       
    WHERE         
    convert(varchar(4),year_for_period) +       
    case when period <10 then '0' + convert(varchar(2),period)       
    else convert(varchar(2),period)       
    end       
    between        
    convert(varchar(4),year(getdate())-1)+       
    case when convert(varchar(4),month(getdate()))<10 then       
    '0' + convert(varchar(4),month(getdate()))       
    else convert(varchar(4),month(getdate()))       
    end       
    AND       
    convert(varchar(4),year(getdate()))+       
    case when convert(varchar(4),month(getdate()))<10 then       
    '0' + convert(varchar(4),month(getdate()))       
    else convert(varchar(4),month(getdate()))       
    end-1     
    AND    
    a.location_id=10052     
    GROUP BY a.location_id, a.inv_mast_uid, a.item_id       
     

    Also, why are you converting the periods in the predicate?  Doing this prohibits the optimizer from using indexes.  You should use integer values, if that is what you column is.  This way your between statement will be SARG compliant and allow index seeks. 
    • Proposed as answer by Adam Haines Wednesday, December 17, 2008 4:17 PM
    • Marked as answer by Adam Haines Monday, March 9, 2009 3:13 PM
    Monday, December 15, 2008 9:31 PM

All replies

  • You can use openrowsource, opendatasource, or openquery to tie directly into the spreadsheet.  Please note that you will not be able to use a jet driver from SQL if you are using a 64 bit version of SQL.  At this point you must load the spreadsheet using SSIS.  Here is how you join the spreadsheet onto the current query.


    INNER JOIN(  
        SELECT *  
        OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
        'Excel 8.0;Database=C:\Documents and Settings\ahaines\Desktop\test.xls', [sheet1$])  
    as Excel  
        ON Excel.MyColumn = a.MyColumn 

    Link to importing data from Excel
    http://support.microsoft.com/kb/321686
    Monday, December 15, 2008 9:14 PM
  • Hi Adam,

    I was plahying around was i was able to upload the data from excel sheet into the PUBS database .

    When i queried select * from readonly.uitems i got the all the items

    I just want to join readonly.uitems to the query and i think i should be able to get all .

    Can you help ????

    SELECT a.location_id, a.inv_mast_uid, a.item_id,  
    sum(a.inv_period_usage) as Actual_Usage_1YR,
    sum(a.number_of_orders) as No_Of_Orders,
    sum(a.filtered_usage) as filtered_usage
    FROM dbo.p21_view_inv_period_usage a  
    INNER JOIN dbo.p21_view_demand_period b ON  
    a.demand_period_uid = b.demand_period_uid   
    WHERE   
    convert(varchar(4),year_for_period) + 
    case when period <10 then '0' + convert(varchar(2),period) 
    else convert(varchar(2),period) 
    end 
    between  
    convert(varchar(4),year(getdate())-1)+ 
    case when convert(varchar(4),month(getdate()))<10 then 
    '0' + convert(varchar(4),month(getdate())) 
    else convert(varchar(4),month(getdate())) 
    end 
    AND 
    convert(varchar(4),year(getdate()))+ 
    case when convert(varchar(4),month(getdate()))<10 then 
    '0' + convert(varchar(4),month(getdate())) 
    else convert(varchar(4),month(getdate())) 
    end-1
    AND
    a.location_id=10052
    GROUP BY a.location_id, a.inv_mast_uid, a.item_id   
    FM
    Monday, December 15, 2008 9:17 PM
  • You can either join the excel data directly onto the existing query or insert the excel data into a table and join that data onto the existing query.  Either way you will have to add another inner join and use the correct on clause to join the data.

    SELECT a.location_id, a.inv_mast_uid, a.item_id,     
    sum(a.inv_period_usage) as Actual_Usage_1YR,   
    sum(a.number_of_orders) as No_Of_Orders,  
    sum(a.filtered_usage) as filtered_usage  
    FROM dbo.p21_view_inv_period_usage a     
    INNER JOIN dbo.p21_view_demand_period b ON     
    a.demand_period_uid = b.demand_period_uid   
    INNER JOIN(  
        SELECT *  
        OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
        'Excel 8.0;Database=C:\Documents and Settings\ahaines\Desktop\test.xls', [sheet1$])  
    as Excel  
        ON Excel.MyColumn = a.MyColumn 
    WHERE      
    convert(varchar(4),year_for_period) +    
    case when period <10 then '0' + convert(varchar(2),period)    
    else convert(varchar(2),period)    
    end    
    between     
    convert(varchar(4),year(getdate())-1)+    
    case when convert(varchar(4),month(getdate()))<10 then    
    '0' + convert(varchar(4),month(getdate()))    
    else convert(varchar(4),month(getdate()))    
    end    
    AND    
    convert(varchar(4),year(getdate()))+    
    case when convert(varchar(4),month(getdate()))<10 then    
    '0' + convert(varchar(4),month(getdate()))    
    else convert(varchar(4),month(getdate()))    
    end-1  
    AND 
    a.location_id=10052  
    GROUP BY a.location_id, a.inv_mast_uid, a.item_id    

    OR

    SELECT a.location_id, a.inv_mast_uid, a.item_id,        
    sum(a.inv_period_usage) as Actual_Usage_1YR,      
    sum(a.number_of_orders) as No_Of_Orders,     
    sum(a.filtered_usage) as filtered_usage     
    FROM dbo.p21_view_inv_period_usage a        
    INNER JOIN dbo.p21_view_demand_period b ON        
    a.demand_period_uid = b.demand_period_uid     
    INNER JOIN readonly.uitems as Excel     
        ON Excel.MyColumn = a.MyColumn    
       
    WHERE         
    convert(varchar(4),year_for_period) +       
    case when period <10 then '0' + convert(varchar(2),period)       
    else convert(varchar(2),period)       
    end       
    between        
    convert(varchar(4),year(getdate())-1)+       
    case when convert(varchar(4),month(getdate()))<10 then       
    '0' + convert(varchar(4),month(getdate()))       
    else convert(varchar(4),month(getdate()))       
    end       
    AND       
    convert(varchar(4),year(getdate()))+       
    case when convert(varchar(4),month(getdate()))<10 then       
    '0' + convert(varchar(4),month(getdate()))       
    else convert(varchar(4),month(getdate()))       
    end-1     
    AND    
    a.location_id=10052     
    GROUP BY a.location_id, a.inv_mast_uid, a.item_id       
     

    Also, why are you converting the periods in the predicate?  Doing this prohibits the optimizer from using indexes.  You should use integer values, if that is what you column is.  This way your between statement will be SARG compliant and allow index seeks. 
    • Proposed as answer by Adam Haines Wednesday, December 17, 2008 4:17 PM
    • Marked as answer by Adam Haines Monday, March 9, 2009 3:13 PM
    Monday, December 15, 2008 9:31 PM
  • Farhan,

    Has your issue been resolved?  If so, please mark the post(s) that helped you solve your issue, as the answer.

    Thanks,
    Adam
    Monday, January 5, 2009 6:35 PM