creating temp table dynamically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

creating temp table dynamically

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 13:39:45

why I am not able to create temp table dynamically?
is there anything I am missing? plz help



BEGIN
DECLARE @SQLSTRING VARCHAR(MAX)
DECLARE @TOTABLENAME VARCHAR(MAX)

SET @TOTABLENAME='RAW'
Select @SqlString ='SELECT * INTO #TEMP FROM '+ @ToTableName +' WHERE 1=2'
print @SQLSTRING
EXEC (@SQLSTRING)
SELECT * FROM #TEMP
END

SELECT * INTO #TEMP FROM raw WHERE 1=2

(0 row(s) affected)
Msg 208, Level 16, State 0, Line 9
Invalid object name '#TEMP'.

-Neil

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 13:46:52

[code]BEGIN
DECLARE @SQLSTRING VARCHAR(MAX)
DECLARE @TOTABLENAME VARCHAR(MAX)

SET @TOTABLENAME='A'
Select @SqlString ='SELECT * INTO #TEMP FROM '+ @ToTableName +' WHERE 1=2; SELECT * FROM #TEMP'
print @SQLSTRING
EXEC (@SQLSTRING)
END[/code]

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 13:51:04

oh how can I create the temp table dynamically in the same session... looks like session issues with temp table

-Neil

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-26 : 14:01:05

quote:

Originally posted by aakcse

why I am not able to create temp table dynamically?
is there anything I am missing? plz help



BEGIN
DECLARE @SQLSTRING VARCHAR(MAX)
DECLARE @TOTABLENAME VARCHAR(MAX)

SET @TOTABLENAME='RAW'
Select @SqlString ='SELECT * INTO #TEMP FROM '+ @ToTableName +' WHERE 1=2'
print @SQLSTRING
EXEC (@SQLSTRING)
SELECT * FROM #TEMP
END

SELECT * INTO #TEMP FROM raw WHERE 1=2

(0 row(s) affected)
Msg 208, Level 16, State 0, Line 9
Invalid object name '#TEMP'.

-Neil

Do you have #TEMP existing?

--------------------------
Get rich or die trying
--------------------------

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 14:14:40

quote:
Originally posted by aakcse

oh how can I create the temp table dynamically in the same session... looks like session issues with temp table

-Neil

Short answer is you don't.

If you can include the code to access your temp table with your dynamic sql you can use it. Otherwise, yeah it's a different session. You might be able to use a global temp table (##temp)..?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 14:16:55

No I do not have #temp table existing

quote:
Originally posted by xhostx

quote:
Originally posted by aakcse

why I am not able to create temp table dynamically?
is there anything I am missing? plz help

BEGIN
DECLARE @SQLSTRING VARCHAR(MAX)
DECLARE @TOTABLENAME VARCHAR(MAX)

SET @TOTABLENAME='RAW'
Select @SqlString ='SELECT * INTO #TEMP FROM '+ @ToTableName +' WHERE 1=2'
print @SQLSTRING
EXEC (@SQLSTRING)
SELECT * FROM #TEMP
END

SELECT * INTO #TEMP FROM raw WHERE 1=2

(0 row(s) affected)
Msg 208, Level 16, State 0, Line 9
Invalid object name '#TEMP'.

-Neil

Do you have #TEMP existing?

--------------------------
Get rich or die trying
--------------------------

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 14:19:22

If you can include the code to access your temp table with your dynamic sql you can use it

How I can do this?

quote:
Originally posted by Lamprey

quote:
Originally posted by aakcse

oh how can I create the temp table dynamically in the same session... looks like session issues with temp table

-Neil
Short answer is you don't.

If you can include the code to access your temp table with your dynamic sql you can use it. Otherwise, yeah it's a different session. You might be able to use a global temp table (##temp)..?

-Neil

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 14:23:11

My post from 06/26/2012 : 13:46:52 shows a highlighted red section; That is "included" code. Bascially, include all the statements that you want to run that use/manipulate the #temp table and include them as part of the dynamic sql string and execute it.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-26 : 14:32:02

(0 row(s) affected)
Msg 208, Level 16, State 0, Line 9
Invalid object name '#TEMP'.

The above error is saying that your #TEMP object doesn't exist, try and resolve your problem step by step.

--------------------------
Get rich or die trying
--------------------------

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 16:02:42

quote:
Originally posted by xhostx

(0 row(s) affected)
Msg 208, Level 16, State 0, Line 9
Invalid object name '#TEMP'.

The above error is saying that your #TEMP object doesn't exist, try and resolve your problem step by step.

Is a scope/session issue. The issues is that the #Temp table doesn't exist in the calling session since it was created/destroyed in the EXEC session.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-26 : 16:04:35

can at ##temp table resolve this issue?

quote:
Originally posted by xhostx

(0 row(s) affected)
Msg 208, Level 16, State 0, Line 9
Invalid object name '#TEMP'.

The above error is saying that your #TEMP object doesn't exist, try and resolve your problem step by step.

--------------------------
Get rich or die trying
--------------------------

-Neil

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-26 : 16:32:30

quote:
Originally posted by aakcse

can at ##temp table resolve this issue?
<snip>
-Neil

Possibly

quote:
Originally posted by Lamprey

Short answer is you don't.

If you can include the code to access your temp table with your dynamic sql you can use it. Otherw
ise, yeah it's a different session. You might be able to use a global temp table (##temp)..?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-27 : 06:09:02

I finally created permanent table to resolve this issue, I was getting errors with Global temp tables as well

table already exists, tempdb cannot be access etc. There is already an object named ‘##temp’ in the database.

-Neil

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-27 : 08:10:36

While using Dynamic sql you can use Temporary table inside that .. Temporary tables are always out of scope in dynamic queries ..

Vijay is here to learn something from you guys.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-27 : 09:16:25

quote:
Originally posted by aakcse

I finally created permanent table to resolve this issue, I was getting errors with Global temp tables as well

table already exists, tempdb cannot be access etc. There is already an object named ‘##temp’ in the database.

-Neil

the reason that you were getting errors when you create the tamp table inside the dynamic sql is that when the scope of the dynamic sql finishes -- the temp tables are discarded. This applies to global temp tables also because there is nothing holding a reference to the table.

You generally should never need to do as you asked.

Creating a permanent table is probably going to cause you concurrency problems if your code is ever executed at the same time by different people....

What are you *actually* trying to do? As in what you want to accomplish at the end of all of this.....

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-27 : 10:23:26

There is a raw file as below with lot of comments section which I have deleted, I have imported this into a raw table with ID(Identity) & Lmp V(max) cols

A2 atusr1
+ atusr2
S9 atusr4,atusr8

Need to clean this and upload to a Normal table as below


Host    User
--------------------
A2	atusr1
+ 	atusr2
S9	atusr4
S9	atusr8

This SP might be executed many a times with diff data file of similar type with varying data

Below are the parameters I would be passing


(@FromTab varchar(300), -- from table
@ToTab varchar(300),   -- to table
@Delimiter varchar(10),  -- separator
@comment varchar(10),   -- comment 
@Header varchar(max)) -- To table column list

Logic i used is as below
1. Drop the To table if it exists, then create it.
2. Created the temp1 table to populate it with raw data ( ID, Lmp)
3. Populated the temp1 table with raw file data
4. Replace all existence of quotes from the data
UPDATE #temp1 SET lmp=REPLACE(lmp,'''','')
5. Remove all the comment lines from the file by delete operation on #temp1 table
6. Then delimiter is replaced with ','

DECLARE @Delim1 varchar(10)
SET @Delim1 = ASCII(@Delim)
UPDATE #temp1 SET lmp=''''+REPLACE(lmp,CHAR(@Delim1),''',''')+''''

7. --Loop through each record and insert it in the @v_ToTable
SET @Lc=1
SET @LoopTotal=(SELECT TOP 1 ID FROM #temp1 ORDER BY ID DESC)

8. before looping I created the physical table which I was trying to do with temporary tables

set @sqlstr ='IF OBJECT_ID(LTRIM(' +'''Phy_Temp'''+ ')) IS NOT NULL DROP TABLE Phy_Temp; CREATE TABLE Phy_Temp ('+@Header+')'
EXEC (@sqlstring)

Then looping


WHILE @Lc<=@LoopTotal 
  BEGIN
        BEGIN   
      SELECT @SQLStr = 
     'INSERT INTO  Phy_Temp
     VALUES (' + LMP + ')'
     FROM #temp1 WHERE ID = @LC  
     EXEC (@sqlstr)
    END 
   SET @Lc=@Lc+1

10.


SELECT @SQLStr=  
'INSERT INTO ' + @v_ToTableName +'
 SELECT t.Host, m.User AS Members
 FROM Phy_Temp t
 CROSS APPLY (SELECT [VALUE] as UserName FROM StringToTab(' +''','''+',t.UserName))m'
 EXEC (@SQLStr)
 END

Here StringToTab function returns the data as below



after while loop insert data in the phy_temp table will be as below( which I was trying to capture in #tmp or ##tmp table )

Host	        User
--------------------
AIX2		atusr1
+ 		atusr2
S9	        atusr4,atusr8
	
-- To clean the above data and put it into actual normal table as below I used last query which as CROSS APPLY  and StringToTab function(which is a table function) I am getting the req result with these steps, it would be good if I am able to use tmp table here instead of phy table to avoide concurrency issue
Host	User
--------------------
AIX2		atusr1
+ 		atusr2
S9	        atusr4
S9	        atusr8

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-27 : 14:40:59

Could someone help me in developing the logic with out using the phy_temp table here? let me know if more info is needed

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-06-28 : 05:34:47

any updated, let me know if more info is needed. I want to replace the PHY_TEMP table with Global temp table

-Neil

Motumoyo
Starting Member

3 Posts

Posted - 2012-06-30 : 00:36:53

I think you should try to solve your problem, step by step.

unspammed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-01 : 01:08:48

i really cant understand why you need to make this dynamic at all. if i understand your scenario correctly, so far as your raw file structure ( fields) remains same, you can use static source and destination tables and avoid the dynamic sql fully. For cleansing data also you can do a set based approach rather a while loop.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-02 : 13:04:51

Hi Visakh

I need to do it dynamically as I am not aware of the target table, it get created in the SP based on the file and number of column.

I have data in the files as below

e.g. file1

#= Tab ( it is actally tab delimited)

A2#actusr1
+#actusr2
S9#actusr4,actusr8

Normalized table should have data as below


C1 C2
----------
A2 Actusr1
+  actusr2
S9 actusr4
S9 actusr8

File2 has data as below (: delimited)

s:!:0:root
stf:!:1:sec,eva,user1,user2,user3,user4,user5,actuse
xyz:!:2:a,b,c
a:!:1:root

Normalize table should have


s	!	0	root
stf	!	sec	eva
stf	!	sec	user1
stf	!	sec	user2
.
.
stf	!	sec	actuse
xyz	!	2	a
xyz	!	2	b
.
.

if you can provide me id I'll email the sp

Thanks

-Neil

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources