Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to create a temp table in a procedure rather than usinh INTO #temptable Expand / Collapse
Author
Message
Posted Thursday, April 10, 2014 7:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:48 AM
Points: 140, Visits: 281
Hello Please.,

I am having a quick question to automate something,


we have 10 procedures in Sql server 2008 R, each procedure having between 2000 lines to 3000 lines

in all procedures the temp tables used,

for example,

Select a.*, b.col1, c.col1, Case when d.col=1 then 'Match' else 'No Match' end as NewColumn
INTO #myTemp1
from
Table1 a join Table2 B on a.col1=b.col1 join Table3 C on a.col1 = c.col2 join Table4 D on b.col1=d.col3


So now the question is we have to change all procedures to create a temp table first then do insert into

So with above example

create table #Mytemp1
(Col1 Varchar(20) NULL,
Col2 Varchar(2) NULL,
Co3 Date
)


Insert into #Mytemp1
Select a.*, b.col1, c.col1, Case when d.col=1 then 'Match' else 'No Match' end as NewColumn
------INTO #myTemp1 Done want to be happen this
from
Table1 a join Table2 B on a.col1=b.col1 join Table3 C on a.col1 = c.col2 join Table4 D on b.col1=d.col3

to do this it is taking big amount of time. also column lengths etc making some issues

so my request to you is.
is there anyway can i do this automatically creation of procedures etc.,

could somebody please help me with this

Thanks a lot in advance
Asiti

Post #1560681
Posted Thursday, April 10, 2014 8:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:48 AM
Points: 140, Visits: 281
Could somebody please help me, any ideas please...
Post #1560694
Posted Friday, April 11, 2014 8:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 36,944, Visits: 31,448
asita (4/10/2014)
Could somebody please help me, any ideas please...


First, there's no way that I know of to do this and any even educated guess at calculated column widths could easily be wrong. That's one of the beauties of SELECT/INTO... you just don't need to know the column widths.

So now the question is we have to change all procedures to create a temp table first then do insert into


WHY? What does someone think this is going to do for you? In (if I dare say) most cases, this is just going to slow your code down.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1560946
Posted Friday, April 11, 2014 8:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 922, Visits: 2,952
WHY? What does someone think this is going to do for you? In (if I dare say) most cases, this is just going to slow your code down


Are there any advantages to creating the table then inserting the data? I've always used

select
x
into #Mytable
from dbo.othertables

but many articles about temp tables seem to go down the create route. It seems you've got more flexibility with what you do to a temp table if you create it first, but is there actually any need?



On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher
Post #1560963
Posted Friday, April 11, 2014 1:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:28 PM
Points: 2,027, Visits: 3,023
Easiest way with accuracy to me is to run the code for each table create without producing any data, then script out the CREATE TABLE statement to copy into the proc. For example:


Select
a.*, b.col1, c.col1, Case when d.col=1 then 'Match' else 'No Match' end as NewColumn
INTO tempdb.dbo.myTemp1 --must use a "real" table to script it out
from Table1 a join
Table2 B on a.col1=b.col1 join
Table3 C on a.col1 = c.col2 join
Table4 D on b.col1=d.col3
where 1 = 0 --add this to prevent any actual rows from being generated


After running that Select, in SSMS, under "tempdb", "Tables", right-click on "dbo.myTemp1" and do "Script Table as", "CREATE To", "New Query Editor Window". Whew. Then copy the "CREATE TABLE" text into the proc and change the table name to your original temp name.

You can then add index(es) or whatever else is needed.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1561116
Posted Friday, April 11, 2014 1:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 314, Visits: 693
One advantage is that Select...Into can lock the database until the insertion is completed.

FWIW I sometimes go with this:

-- Step 1. Create target table
select <whatever>
into #target
where 1 = 0

...
-- Step 2. Populate target table
Insert into #target
<whatever>

This can be handy if you have a "template" table you can use for the first step and you want to populate the target table from a stored proc as in

Insert into #target
exec <stored proc that returns a table>

Post #1561119
Posted Friday, April 11, 2014 6:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 36,944, Visits: 31,448
Gerald.Britton 28181 (4/11/2014)
One advantage is that Select...Into can lock the database until the insertion is completed.


Across linked servers, yes, and it does put certain shared locks on TempDB that make is so SSMS times out on TempDB in the explorer window IF you're trying to do something with TempDB there, but it hasn't locked up databases for normal usage since there was a hot fix way back in SQL Server 6.5.

Don't take my word for it, though. Here's the MS document. Please notice the part where it states "NOTE: This problem does not apply to SQL Server 7.0 and later.".
http://support.microsoft.com/kb/153441


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561157
Posted Wednesday, April 16, 2014 7:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 314, Visits: 693
Well, the fix is not complete. I ran into this again in SQL Server 2008R2 just last year. I applied my workaround and voila! Lock gone.
Post #1562285
Posted Saturday, April 19, 2014 3:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 36,944, Visits: 31,448
gbritton1 (4/16/2014)
Well, the fix is not complete. I ran into this again in SQL Server 2008R2 just last year. I applied my workaround and voila! Lock gone.


Do you happen to remember what the code was doing during the SELECT/INTO?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563293
Posted Monday, April 21, 2014 10:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 314, Visits: 693
Jeff Moden (4/19/2014)
gbritton1 (4/16/2014)
Well, the fix is not complete. I ran into this again in SQL Server 2008R2 just last year. I applied my workaround and voila! Lock gone.


Do you happen to remember what the code was doing during the SELECT/INTO?


I don't remember it exactly. It was just a typical

select ...
into ...

I replaced it with a two-part:

select ... 
into ...
where 1 = 0

insert into ...
select ...

The difference was immediate, tangible and measurable. The first example prevented other queries with default transaction isolation levels from hitting the database until the insert was done. The second did not.
Post #1563557
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse