Solved

Global Temp Vs Temp - help...

Posted on 2008-06-13
6
226 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
  • 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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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