How to create a temp table in a procedure rather than usinh INTO #temptable

Question

Post reply

How to create a temp table in a procedure rather than usinh INTO #temptable

asita

SSCarpal Tunnel

Points: 4144
More actions
April 10, 2014 at 7:20 pm

#396584

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

asita SSCarpal Tunnel Points: 4144 More actions · Answer 1

Could somebody please help me, any ideas please...

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Neil Burton SSC-Insane Points: 22999 More actions · Answer 3

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

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

ScottPletcher SSC Guru Points: 101097 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

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>

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gbritton1 SSCertifiable Points: 6522 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gbritton1 SSCertifiable Points: 6522 More actions · Answer 9

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.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

gbritton1 (4/21/2014)
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.

Thank you very much for the feedback on that. I've never had that problem before. You certainly did the alternative the right way.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)