• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Global Temp Vs Temp - help...

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
tbaseflug
Asked:
tbaseflug
  • 3
  • 3
1 Solution
 
tbaseflugAuthor Commented:
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
 
chapmandewCommented:
Yep, it goes out to scope....
0
 
chapmandewCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
tbaseflugAuthor Commented:
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
 
chapmandewCommented:
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
 
tbaseflugAuthor Commented:
Perfect - thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now