Into Clause not working 1

[phil22046]

I have a large SQL statement with a Union to another large SQL stmt. When I run it I see the data in the results pane of query analyzer. I want to put the results into a temp table to be able to write additional sql statements to update another table. Also to speed up execution I should index some of the fields. Everytime I try and place "INTO #tempfile" or a similar INTO .dbo.Temp_workfile" in the large SQL statement, I get an error.

 

[gmmastros]

This can be done, but before I show you how, let me ask you a question.

Do you understand the difference between Union and Union All?

When you use Union, sql server will only return distinct records. When you use Union All, sql server will return all the records, even if there would be duplicates.

Why is this important? Well... If you want to only insert distinct records in to the temp table, then you have to use a derived table. Like this...

[tt][blue]
Select Col1, Col2
Into #TempTable
From (
Select Col1, Col2 From SomeTable1
Union
Select Col2, Col2 From SomeTable2
) As SomeAlias
[/blue][/tt]

If you don't care about distinct records, or you know they will be distinct anyway, then you can do this...

Select Col1, Col2
Into   #TempTable
From   SomeTable1

Insert 
Into   #TempTable(Col1, Col2)
Select Col1, Col2
From   SomeTable2

Union All performs better than Union because sql server does not have to determine the distinct rows.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

I get an error

Will you be sharing the error with us?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[phil22046]

ca8msm, it was a syntax error.