?
Solved

Global Temp Vs Temp - help...

Posted on 2008-06-13
6
Medium Priority
?
230 Views
Last Modified: 2010-04-21
I have the below - and when I use a global temp table it works but when I create a normal temp table right above the dynamic sql - it does not insert into - is it out of scope or something:

My global temp table is: ##tblMXPROFIT_CASH_CON1TEST

When I try and simply create a #tblMXPROFIT_CASH_CON1TEST temp table, and replace in the below, it does not work?
EXEC
    ( 'SELECT a.DEPT_CODE, a.PROC_CODE, aa.INSURANCE, aa.FSC, aa.PATIENT_TP, aa.PTP, c.UNIT_PRICE, a.QTY, a.REV 
			INTO ##tblMXPROFIT_CASH_CON1TEST
			FROM ' + @DB + '_vwMX_TRANS a INNER JOIN ' + @DB
      + '_vwMX_HEAD aa 
				ON a.ACCT_NO = aa.ACCT_NO 
				LEFT JOIN #tmp c 
				ON a.DEPT_CODE = c.DEPT_CODE AND a.PROC_CODE = c.PROC_CODE 
			ORDER BY aa.INSURANCE, aa.FSC, aa.PATIENT_TP, aa.PTP ' )

Open in new window

0
Comment
Question by:tbaseflug
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 

Author Comment

by:tbaseflug
ID: 21782991
Could I do something like:


SELECT * INTO #tblMXPROFIT_CASH_CON1TEST
EXEC
    ( 'SELECT a.DEPT_CODE, a.PROC_CODE, aa.INSURANCE, aa.FSC, aa.PATIENT_TP, aa.PTP, c.UNIT_PRICE, a.QTY, a.REV 
			FROM ' + @DB + '_vwMX_TRANS a INNER JOIN ' + @DB
      + '_vwMX_HEAD aa 
				ON a.ACCT_NO = aa.ACCT_NO 
				LEFT JOIN #tmp c 
				ON a.DEPT_CODE = c.DEPT_CODE AND a.PROC_CODE = c.PROC_CODE 
			ORDER BY aa.INSURANCE, aa.FSC, aa.PATIENT_TP, aa.PTP ' )

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21782992
Yep, it goes out to scope....
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21783009
No, you can't. However, if you declare the definition of the temp table and then try to insert into it, it shoudl work.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:tbaseflug
ID: 21783019
Something like the below?  
CREATE TABLE #tblMXPROFIT_CASH_CON1TEST  (
DEPT_CODE nvarchar(10),
PROC_CODE nvarchar(10),
INSURANCE NVARCHAR(10),
FSC NVARCHAR(10),
PATIENT_TP NVARCHAR(10),
PTP NVARCHAR(10),
UNIT_PRICE MONEY,
QTY INT,
REV INT
)
 
INSERT INTO #tblMXPROFIT_CASH_CON1TEST 
EXEC
    ( 'SELECT a.DEPT_CODE, a.PROC_CODE, aa.INSURANCE, aa.FSC, aa.PATIENT_TP, aa.PTP, c.UNIT_PRICE, a.QTY, a.REV 
			FROM ' + @DB + '_vwMX_TRANS a INNER JOIN ' + @DB
      + '_vwMX_HEAD aa 
				ON a.ACCT_NO = aa.ACCT_NO 
				LEFT JOIN #tmp c 
				ON a.DEPT_CODE = c.DEPT_CODE AND a.PROC_CODE = c.PROC_CODE 
			ORDER BY aa.INSURANCE, aa.FSC, aa.PATIENT_TP, aa.PTP ' )

Open in new window

0
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 21783052
Kinda...something like this...you have to assign your script to a var first....make sure your @db variable has a value also.


CREATE TABLE #tblMXPROFIT_CASH_CON1TEST  (
DEPT_CODE nvarchar(10),
PROC_CODE nvarchar(10),
INSURANCE NVARCHAR(10),
FSC NVARCHAR(10),
PATIENT_TP NVARCHAR(10),
PTP NVARCHAR(10),
UNIT_PRICE MONEY,
QTY INT,
REV INT
)
DECLARE @SQL NVARCHAR(2000)
SET @SQL = 'SELECT a.DEPT_CODE, a.PROC_CODE, aa.INSURANCE, aa.FSC, aa.PATIENT_TP, aa.PTP, c.UNIT_PRICE, a.QTY, a.REV
                        FROM ' + @DB + '_vwMX_TRANS a INNER JOIN ' + @DB
      + '_vwMX_HEAD aa
                                ON a.ACCT_NO = aa.ACCT_NO
                                LEFT JOIN #tmp c
                                ON a.DEPT_CODE = c.DEPT_CODE AND a.PROC_CODE = c.PROC_CODE
                        ORDER BY aa.INSURANCE, aa.FSC, aa.PATIENT_TP, aa.PTP '
 
INSERT INTO #tblMXPROFIT_CASH_CON1TEST
EXEC sp_executesql @SQL
0
 

Author Closing Comment

by:tbaseflug
ID: 31467092
Perfect - thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question