Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    45

    Unanswered: deciphering the into key work in this sql

    Hello all,

    I am just trying to decipher this sql and the only thing I do not understand what is going on here is what the "input" key word is doing below. I believe its loading all of the select into the table ##OrderEmails, what is it doing? am I correct?

    Code:
    			SELECT 
    			LEFT(OAORDT,4) + '-' + SUBSTRING(RTRIM(OAORDT),5,2) +'-' + RIGHT(OAORDT,2) "Date",
    			OACONO Company, 
    			OADIVI Division, 
    			COUNT(DISTINCT oaorno) "# Orders"
    			--/*OAORST + '/' +*/ OAORSL "Order Status"
    			INTO ##OrderEmails
    			FROM [BI-Datamart].M3EDBPRD.OOHEAD
    
    			WHERE OAORDT BETWEEN
    			(RTRIM(YEAR(DATEADD(DAY,-7,CURRENT_TIMESTAMP))) + 
    			RIGHT('00' +RTRIM(MONTH(DATEADD(DAY,-7,CURRENT_TIMESTAMP))),2) +
    			RIGHT('00' +RTRIM(DAY(DATEADD(DAY,-7,CURRENT_TIMESTAMP))),2))
    			AND 
    			(RTRIM(YEAR(DATEADD(DAY,-1,CURRENT_TIMESTAMP))) + 
    			RIGHT('00' +RTRIM(MONTH(DATEADD(DAY,-1,CURRENT_TIMESTAMP))),2) +
    			RIGHT('00' +RTRIM(DAY(DATEADD(DAY,-1,CURRENT_TIMESTAMP))),2))
    			AND oaorsl < 90 AND OADIVI = '001'
    			GROUP BY 
    			OAORDT, OACONO, OADIVI
    			ORDER BY OAORDT DESC, OACONO ASC, OADIVI ASC

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you mean the part highlighted in red:
    Code:
    SELECT
       LEFT(OAORDT,4) + '-' + SUBSTRING(RTRIM(OAORDT),5,2) +'-' + RIGHT(OAORDT,2) "Date",
       OACONO Company, OADIVI Division, COUNT(DISTINCT oaorno) "# Orders"
    -- /*OAORST + '/' +*/ OAORSL "Order Status"
       INTO ##OrderEmails
       FROM [BI-Datamart].M3EDBPRD.OOHEAD
       WHERE OAORDT BETWEEN
          (RTRIM(YEAR(DATEADD(DAY,-7,CURRENT_TIMESTAMP))) + 
             RIGHT('00' +RTRIM(MONTH(DATEADD(DAY,-7,CURRENT_TIMESTAMP))),2) +
             RIGHT('00' +RTRIM(DAY(DATEADD(DAY,-7,CURRENT_TIMESTAMP))),2))
          AND 
          (RTRIM(YEAR(DATEADD(DAY,-1,CURRENT_TIMESTAMP))) + 
             RIGHT('00' +RTRIM(MONTH(DATEADD(DAY,-1,CURRENT_TIMESTAMP))),2) +
             RIGHT('00' +RTRIM(DAY(DATEADD(DAY,-1,CURRENT_TIMESTAMP))),2))
             AND oaorsl < 90 
    		 AND OADIVI = '001'
       GROUP BY OAORDT,      OACONO,     OADIVI
       ORDER BY OAORDT DESC, OACONO ASC, OADIVI ASC
    ...then yes, it does send the result set from this SELECT statement into the global temporary table named ##OrderEmails

    Note that this may well work, but it does so in a really, REALLY unsafe way. This code construct can cause a whole litany of problems ranging from serious problems like SQL Server freezing so that no SQL will run at all on the server due to locks held in tempdb, to minor problems like mixed data when two processes try to execute this SQL at the same time. I would very strongly recommend that you find another way to get this done that isn't potentially dangerous to your server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2007
    Posts
    45
    Thanks for the update,

    I suppose the best way to handle this is by using a insert(#,#,...) select #, #, ... from table_name

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Finding the correct answer depends on knowing quite a bit about your data and how you plan to use that data. Only someone with intimate knowledge of what you're trying to do can answer those questions, and the odds are pretty high that you are that person!

    If you only expect one spid (connection to your SQL Server) to use the data, then use a single # table. That is safe.

    If you expect multiple spids to use the data in the table, then that should probably be a normal table instead of a temporary table.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •