SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Temporary Storage
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 07/12/2013 :  13:01:33  Show Profile  Reply with Quote
Hi

Im running the below query and its running more than 3 hrs.but still there is no single records found in the table. the source table has
60 million records apprx.

Now my question is where the storage is happening temporarily.I believe it is in temporary database (tempdb).if that's the case how to check the table's from it.

insert into dbo.Dat_MRC_FAGLFLEXB_FY1333333
				(	Account            
                   , Company             
                   , CostCtr            
                   , ProfitCtr          
                   , Product            
                   , Market             
                   , Format             
                   , Territory          
                   , SourceCode         
                   , RollForwardActivity
                   , DocType            
                   , ReferenceYear      
                   , Customer           
                   , FilmDimension      
                   , Vendor             
                   , InternalOrder      
                   , TradingPartner  
					,Currency
                   , FiscalYr           
                   , Period
                   , FTCurr
                   , Turnover
                   , FISCPER
                   ,Count

                 )

  SELECT           Account            
                   , Company             
                   , CostCtr            
                   , ProfitCtr          
                   , Product            
                   , Market             
                   , Format             
                   , Territory          
                   , SourceCode         
                   , RollForwardActivity
                   , DocType            
                   , ReferenceYear      
                   , Customer           
                   , FilmDimension      
                   , Vendor             
                   , InternalOrder      
                   , TradingPartner     
             --      , SUM( Currency ) AS CURTYPE                           -- For testing only comment out in production
                  , LEFT( CURRUNITforCURTYPE__LocCcy_DocCcy, 3 )
                     + CASE SUM( Currency )
                            WHEN 1
                            THEN ' DocCcy-s'
                            WHEN 2
                            THEN ' LocCcy-s'
                            WHEN 4
                            THEN ' GrCcy-s'
                            WHEN 8
                            THEN ' GlCcy-s'
                            WHEN 3
                            THEN ' LocDocCcy-c' 
                            WHEN 5
                            THEN ' GrDocCcy-c'
                            WHEN 6
                            THEN ' GrLocCcy-c'
                            WHEN 7
                            THEN ' GRLocDocCcy-c'
                            WHEN 9
                            THEN ' GlDocCcy-c'
                            WHEN 10
                            THEN ' GlLocCcy-c'
                            WHEN 11
                            THEN ' GlLocDocCcy-c'
                            ELSE 'Error - SUM( Currency ) = ' + CAST( SUM( Currency ) AS nvarchar(10) ) + ' should never exist as a possible 1,2,3 way sum of the values 1,2,4,8'
                        END AS Currency
                   , FiscalYr           
                   , Period
                   , FTCurr
                   , Turnover
                   , FISCPER
       --          , CURRUNITforCURTYPE__LocCcy_DocCcy                    -- For testing only comment out in production
                   , COUNT(*) AS DuplicateCount                             -- For testing only but DO NOT comment out in production needed for reconcilliation
           FROM (
           SELECT 
CASE WHEN RACCT = '' OR RACCT IS NULL THEN 'No_Account' ELSE 'AC_' + RACCT + '' END  AS Account
,CASE WHEN RBUKRS = '' OR RBUKRS IS NULL THEN 'No_CoCode' ELSE 'CO_' + RBUKRS + '' END  AS Company
,CASE WHEN RCNTR = '' OR RCNTR IS NULL THEN 'No_CostCenter' ELSE 'CC_' + RCNTR + '' END  AS CostCtr
,CASE WHEN PRCTR = '' OR PRCTR IS NULL THEN 'No_ProfitCenter' ELSE 'PC_' + PRCTR + '' END  AS ProfitCtr
,CASE WHEN ZZMPMPRD = '' OR ZZMPMPRD IS NULL THEN 'No_Product' ELSE /*'PR_' + */  right(ZZMPMPRD, len(ZZMPMPRD)+1 - patindex('%[^0]%', ZZMPMPRD)) /*+ ''*/ END  AS Product
,CASE WHEN ZZMARKET = '' OR ZZMARKET IS NULL THEN 'No_Market' ELSE 'MK_' + ZZMARKET + '' END  AS Market
,CASE WHEN ZZFORMAT = '' OR ZZFORMAT IS NULL THEN 'No_Format' ELSE 'FT_' + ZZFORMAT + '' END  AS Format
,CASE WHEN ZZTERTRY = '' OR ZZTERTRY IS NULL THEN 'No_Territory' ELSE 'TR_' + ZZTERTRY + '' END  AS Territory
,CASE WHEN ZZSRCSYS = '' OR ZZSRCSYS IS NULL THEN 'No_System' ELSE 'SY_' + ZZSRCSYS END  AS SourceCode
,CASE WHEN ZZRFACT = '' OR ZZRFACT IS NULL THEN 'No_RollForwardActivity' ELSE 'RA_' + ZZRFACT + '' END  AS RollForwardActivity
,CASE WHEN ZZBLART = '' OR ZZBLART IS NULL THEN 'No_DocType' ELSE 'DT_' + ZZBLART END  AS DocType
,CASE WHEN STR(ZZREFYR,4,0) = '   0' OR ZZREFYR IS NULL THEN 'No_ReferenceYear' ELSE 'RY_' + STR(ZZREFYR,4,0) + '' END  AS ReferenceYear
,CASE WHEN ZZSPKUNNR = '' OR ZZSPKUNNR IS NULL THEN 'No_Customer' ELSE 'CN_' + ZZSPKUNNR + '' END  AS Customer
,CASE WHEN ZZFLMDIM = '' OR ZZFLMDIM IS NULL THEN 'No_FilmDimension' ELSE '' + ZZFLMDIM + '' END  AS FilmDimension
,CASE WHEN ZZVENDOR = '' OR ZZVENDOR IS NULL THEN 'No_Vendor' ELSE 'VN_' + ZZVENDOR + '' END  AS Vendor
,CASE WHEN ZZAUFNR = '' OR ZZAUFNR IS NULL THEN 'No_InternalOrder' ELSE 'IO_' + ZZAUFNR + '' END  AS InternalOrder
,CASE WHEN RASSC = '' OR RASSC IS NULL THEN 'No_TradingPartner' ELSE 'TP_' + right(RASSC, len(RASSC)+1 - patindex('%[^0]%', RASSC)) + '' END  AS TradingPartner
,CASE WHEN CURTYPE = '' OR CURTYPE IS NULL THEN NULL ELSE CASE CURTYPE WHEN '00' THEN 1 WHEN '10' THEN 2 WHEN '30' THEN 4 WHEN '60' THEN 8               ELSE 1000 + CURTYPE                   END END AS Currency
,CASE WHEN CURTYPE = '' OR CURTYPE IS NULL THEN 'No_CurrencyType' ELSE 'USD-GrCcy, '  +  CASE CURTYPE WHEN '00' THEN ''       WHEN '10' THEN ''       WHEN '30' THEN ''      WHEN '60' THEN CURRUNIT + '-GlCcy, ' ELSE CURRUNIT + '-' + CURTYPE + '-Ccy, ' END 
END + FunctionalCurrency + '-LocCcy, ' + RTCUR + '-DocCcy' AS FTCurr
,CASE WHEN FISCPER = 0 OR FISCPER IS NULL THEN 'No_FiscalYr' ELSE 'FY' + substring(str(FISCPER,7,0),3,2) + '' END  AS FiscalYr
,CASE WHEN FISCPER = 0 OR FISCPER IS NULL THEN 'No_Period' WHEN charindex(RIGHT(str(FISCPER,7,0),2),'01020304050607080910111200')=25 THEN 'BegBalance' ELSE substring( 'OctNovDecJanFebMarAprMayJunJulAugSep' , charindex( '-'+RIGHT(str(FISCPER,7,0),2) , '-01
-02-03-04-05-06-07-08-09-10-11-12-00' ), 3 ) END  AS Period
,FISCPER
,sum(TURNOVER) AS Turnover
,CURRUNIT + FunctionalCurrency + RTCUR AS CURRUNITforCURTYPE__LocCcy_DocCcy
 FROM ( select  * from Dat_EAI_FAGLFLEXB WITH (NOLOCK)) q
 LEFT OUTER JOIN DimDetailCompanyVw
 on RBUKRS = Company 
 WHERE ( (DEBIT IS NOT NULL AND ABS(DEBIT) >.00000001) OR (CREDIT IS NOT NULL AND ABS(CREDIT) >.00000001) OR (TURNOVER IS NOT NULL AND ABS(TURNOVER) >.00000001))
AND ( CURTYPE = '00' OR CURTYPE = '10' OR CURTYPE = '30' )
AND CURRUNIT IS NOT NULL AND RTCUR IS NOT NULL and CURRUNIT <> '' AND RTCUR <> ''
and fiscper like '2013%'


Please correct me if i'm wrong..

James K
Flowing Fount of Yak Knowledge

3576 Posts

Posted - 07/12/2013 :  14:36:27  Show Profile  Reply with Quote
Take a look at the queries on this MSDN page, which tells you how to identify the space usage by databases, specific queries etc. http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000