Does SQL Server creates some temporary table during query execution?

Hi,
I want to know when does SQL Server use temporary tables for query
processing?
Does it use for all queries or for some complex queries? Or doesn't use at
all?
Thanks
PushkarYes, it does. If you show the query plan, you'll see icons such as Table
Spool/Eager Spool. That implies that a temporary table is being created by
the optimizer behind the scenes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
Hi,
I want to know when does SQL Server use temporary tables for query
processing?
Does it use for all queries or for some complex queries? Or doesn't use at
all?
Thanks
Pushkar|||Pushkar
I think SQL Server decides internally to perform some operations in tempdb
database. For sure I know that if your query has ORDER BY ,GROUP BY clauses
and it has to operate in large amount of data , so SQL Server will create a
work tables to perfom that.
I'd suggest to visit at Aaron's web site to get more explanation
www.aspfaq.com
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to know when does SQL Server use temporary tables for query
> processing?
> Does it use for all queries or for some complex queries? Or doesn't use at
> all?
> Thanks
> Pushkar
>|||Thanks !!!
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urnhSSiNGHA.2916@.tk2msftngp13.phx.gbl...
> Pushkar
> I think SQL Server decides internally to perform some operations in
> tempdb database. For sure I know that if your query has ORDER BY ,GROUP BY
> clauses and it has to operate in large amount of data , so SQL Server will
> create a work tables to perfom that.
> I'd suggest to visit at Aaron's web site to get more explanation
> www.aspfaq.com
>
>
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
>

Does Commit Transaction delete Temporary table?

Hi SQL Guru's,
I create a global temp table in one of my procedures:
select * into ##temp_update
from ( select distinct * from .....
after this I issue a commit transaction, and when I try to select from
the table I created (from my .Net code or Query Analyzer) it is not
there!
select * from ##temp_update
Invalid object name '##temp_update'.
Any idea why?From Books Online:
"Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing them. Th
e
association between a task and a table is maintained only for the life of a
single Transact-SQL statement. This means that a global temporary table is
dropped at the completion of the last Transact-SQL statement that was
actively referencing the table when the creating session ended."
If the session that creates the global temporary table ends before a new
session that accesses the global temporary table is started, the table is
dropped.
ML|||Yikes!
Good to know that, but bad for my program :)
thanks much
ML wrote:
> From Books Online:
> "Global temporary tables are automatically dropped when the session that
> created the table ends and all other tasks have stopped referencing them.
The
> association between a task and a table is maintained only for the life of
a
> single Transact-SQL statement. This means that a global temporary table is
> dropped at the completion of the last Transact-SQL statement that was
> actively referencing the table when the creating session ended."
> If the session that creates the global temporary table ends before a new
> session that accesses the global temporary table is started, the table is
> dropped.
>
> ML

Does Commit Transaction delete Temporary table?

Hi SQL Guru's,
I create a global temp table in one of my procedures:
select * into ##temp_update
from ( select distinct * from .....
after this I issue a commit transaction, and when I try to select from
the table I created (from my .Net code or Query Analyzer) it is not
there!
select * from ##temp_update
Invalid object name '##temp_update'.
Any idea why?From Books Online:
"Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing them. Th
e
association between a task and a table is maintained only for the life of a
single Transact-SQL statement. This means that a global temporary table is
dropped at the completion of the last Transact-SQL statement that was
actively referencing the table when the creating session ended."
If the session that creates the global temporary table ends before a new
session that accesses the global temporary table is started, the table is
dropped.
ML|||Yikes!
Good to know that, but bad for my program :)
thanks much
ML wrote:
> From Books Online:
> "Global temporary tables are automatically dropped when the session that
> created the table ends and all other tasks have stopped referencing them.
The
> association between a task and a table is maintained only for the life of
a
> single Transact-SQL statement. This means that a global temporary table is
> dropped at the completion of the last Transact-SQL statement that was
> actively referencing the table when the creating session ended."
> If the session that creates the global temporary table ends before a new
> session that accesses the global temporary table is started, the table is
> dropped.
>
> ML

Do you understand this Error msg (ERROR Msg 512, Level 16, State 1, Line 33)

I have created a procedure which start by fecthing data from DB-X and put in into the temporary memory. what im trying to do now is to take data from temporary memory insert/update DB-Y.

But now I get this ERROR Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

HERE IS MY PROCEDURE

DECLARE @.MineID int,
@.MineName varchar(80),
@.MineDescription [varchar](80) ,
@.MineLocation varchar(80),
@.Country varchar(80),
@.Northing float,
@.Easting float,
@.Elevation float,
@.Latitude float ,
@.Longitude float,
@.MineLogo varbinary(max)

DECLARE MYCURSOR CURSOR
FOR SELECT * FROM [TLC].[DBO].[MINE]
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO
@.MineID,
@.MineName ,
@.MineDescription ,
@.MineLocation ,
@.Country ,
@.Northing ,
@.Easting ,
@.Elevation ,
@.Latitude,
@.Longitude,
@.MineLogo
WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.MineID =(select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
BEGIN UPDATE [TEST].[dbo].[MINE1]
SET MineName = @.MineName
,MineDescription = @.MineDescription
,MineLocation = @.MineLocation
,Country =@.Country
,Northing = @.Northing
,Easting = @.Easting
,Elevation = @.Elevation
,Latitude = @.Latitude
,Longitude = @.Longitude
,MineLogo =@.MineLogo
where MineId = @.MineID

END
IF @.MineID <> (select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
INSERT INTO [TEST].[dbo].[MINE1]
(MineID
,MineName
,MineDescription
,MineLocation
,Country
,Northing
,Easting
,Elevation
,Latitude
,Longitude
,MineLogo )
VALUES (
@.MineID
,@.MineName
,@.MineDescription
,@.MineLocation
,@.Country
,@.Northing
,@.Easting
,@.Elevation
,@.Latitude
,@.Longitude
,@.MineLogo)

FETCH NEXT FROM MYCURSOR INTO

@.MineID,
@.MineName ,
@.MineDescription ,
@.MineLocation ,
@.Country ,
@.Northing ,
@.Easting ,
@.Elevation ,
@.Latitude,
@.Longitude,
@.MineLogo

END
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
CLOSE MYCURSOR
DEALLOCATE MYCURSORYou don't need cursors for this purpose. Please look at following sample that demonstrates how you should synchronize your data:

create database temp
create database temp1
go
create table temp.dbo.mine(id int identity, name nvarchar(max))
create table temp1.dbo.mine(id int identity, name nvarchar(max))

insert temp.dbo.mine(name) values('UpdatedValue1')
insert temp.dbo.mine(name) values('UpdatedValue2')
insert temp.dbo.mine(name) values('UpdatedValue3')
insert temp.dbo.mine(name) values('UpdatedValue4')
insert temp.dbo.mine(name) values('UpdatedValue5')
insert temp.dbo.mine(name) values('NewValue6')
insert temp1.dbo.mine(name) values('OldValue1')
insert temp1.dbo.mine(name) values('OldValue2')
insert temp1.dbo.mine(name) values('OldValue3')
insert temp1.dbo.mine(name) values('OldValue4')
insert temp1.dbo.mine(name) values('OldValue5')
select * from temp.dbo.mine
select * from temp1.dbo.mine

update temp1.dbo.mine
set
name = Source.name
from temp.dbo.mine Source
where mine.id = Source.id

set identity_insert temp1.dbo.mine on
insert temp1.dbo.mine(id, name)
select id, name
from temp.dbo.mine
where id not in (select id from temp1.dbo.mine)
set identity_insert temp1.dbo.mine off

select * from temp.dbo.mine
select * from temp1.dbo.mine

drop database temp
drop database temp1|||Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test. And these two database they are not integrated and we can't intergrate them.So what will happen is: if the data exist it can update else insert a new record. I'm not creating database from scratch.

this error is trigged by these statemement

IF @.BlasterID <> (select BlasterID from Blasters )
INSERT INTO [TEST].[dbo].[Blasters]

BELOW IS THE ERROR
{Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.}

It seems like SQL doesn't want that , I have also tried the IF NOT EXIST clause but still it doesn't help. Please if you have any ideas agains on how to handle this with SQL please help|||

Quote:

Originally Posted by Sally1053

Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test

Have you looked at sample I provided? It demonstrates how to synchronize data from two different databases in more efficient way than you do.
--
Concerning your particular problem with
IF @.BlasterID <> (select BlasterID from Blasters ):
You can compare a variable (@.BlasterID) with resultset (select BlasterID from Blasters) only when resultset contains a single row with single column. In your case resultset contains all rows from Blasters table, not one. Correct solution is:
IF NOT EXISTS (select * from Blasters WHERE BlasterID = @.BlasterID)