Temp Table - SQL Server Forums

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

Temp Table

Author

Topic

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 04:01:54

Hi All,

Is there anything like

CREATE TABLE
temptable
AS
SELECT column1,column2... FROM table1 ...

I know there

SELECT column1,... INTO temptable FROM table1....

How would I go about this when I have

SELECT columns1,....FROM table1...
UNION
SELECT columns1,...FROM tableA...
UNOIN
SELECT columns1...FROM tableN..
UNOIN
.........

Into a TempTable.

Necessity is the mother of all inventions!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-14 : 04:03:43

[code]Create Table #temp
(
... -- column definitions here
)

INSERT INTO #temp
SELECT columns1,....FROM table1...
UNION
SELECT columns1,...FROM tableA...
[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-14 : 04:04:15

[code]
SELECT column1,... INTO temptable
FROM
(
SELECT columns1,....FROM table1...
UNION
SELECT columns1,...FROM tableA...
) d
[/code]

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 04:09:15

Create Table #temp
(
... -- column definitions here
)

INSERT INTO #temp
SELECT columns1,....FROM table1...
UNION
SELECT columns1,...FROM tableA...
-------------

This is wat I am doing now. So seems like a lot of code.. coz there many such tables to be created. On the other hand

SELECT colum1...INTO temptable
FROM
(
SELECT columns1.. FROM table1..
UNOIN
SELECT columns1..FROM tableA..
)d

Will mean I need to have all the columns in the SELECT columns1.... INTO Temptable

Is that correct?

Necessity is the mother of all inventions!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-14 : 04:18:43

quote:
Will mean I need to have all the columns in the SELECT columns1.... INTO Temptable

Yes.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-14 : 04:21:29

Even though it has lot of code involved, it prevents locks on system tables when creating temp table which is the case with SELECT..INTO approach.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 04:28:41

Even though it has lot of code involved, it prevents locks on system tables when creating temp table which is the case with SELECT..INTO approach.

I wasnt aware of it locking system tables. harsh can you give me a example. Better still if you show .. wat are effects due to this. Thank you

Necessity is the mother of all inventions!

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 04:51:12

And to add to the same .. INSERT INTO temptable costs 50% more then SELECT INTO on the test which I did with a query

The results are like 9:34 mins for SELECT INTO and 14:46 mins for INSERT INTO without the CREATE TABLE .
Any ideas, as what to use since I am trying to reduce the time taken for the Execution. And I am still concerned abt the locks
SELECT INTO will impose.
Thank you

Necessity is the mother of all inventions!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 05:02:56

What is the UNION for?
If there are no duplicates, add ALL to UNION and see if it runs faster.

Peter Larsson
Helsingborg, Sweden

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 05:15:08

Peso.. there are duplicates..so I will have to stick with UNION

Necessity is the mother of all inventions!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 05:24:18

I think UNION filters out the dupicates for each and every time it's used.
Try this and see if there is a speed difference.

INSERT TempTable
SELECT DISTINCT column1, column2...
FROM
(
SELECT column1, column2... FROM table1
UNION ALL
SELECT column1, column2... FROM tableA
) as d

Peter Larsson
Helsingborg, Sweden

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 07:13:20

hey, peso tried that.. it is running for more than 35 mins

Necessity is the mother of all inventions!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-05-15 : 19:34:05

Find and delete the dupes after the table is populated... it's quicker.

--Jeff Moden

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-16 : 04:08:05

Jeff .. that is a good idea.. wat do u think of

SELECT col1,col2,col3.........COUNT(1) INTO temptable2 FROM temptable1
GROUP BY col1,col2,col3.....

DROP TABLE temptable1

and use temptable2

Necessity is the mother of all inventions!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-05-17 : 01:27:33

That's one way to do it... but if it's taking 9 or 10 minutes just to do the original insert, you're gonna have a lot more time involved doing it that way. How many rows of data in the table are we talking about? And, can we make one of the columns in the table an IDENTITY column? If so, I have a method for deleting 18,000 dupes from a 4 million row table in about a minute, depending on your disk setup and ram, of course.

--Jeff Moden

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-18 : 04:50:29

Jeff , now I am doing


SELECT col1...INTO temptable FROM table1....
UNION
SELECT col1...    FROM table2...
UNION
SELECT col1..     FROM table3....

This is seems to be faster too.. And the other thing about deleting .. from a table which is index it will be easiler but not a temptable. That is slower than this.

Necessity is the mother of all inventions!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources