• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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