October 31, 2009 at 9:00 am
I have a query.
This is the situation:
create table #temp1(
col1 int
)
create table #temp2(
col2 int
)
create table #temp3(
col3 int
)
insert into #temp1
select col2 from #temp2
Union All
Select col3 from #temp3
In this case, what happens?
Whether the data from #temp2 will be inserted into the table #temp1 first and then the data from #temp3 and after making the union all of both #temp2 and #temp3 and then the data is inserted into #temp1?
October 31, 2009 at 9:31 am
Basically it will be indeterminate. If could arrive in any order. If you need a specific order then you HAVE to specify order by.
October 31, 2009 at 9:36 am
no no... You didnt get my question...
The order doesnt matter to me.
Just imagine that these are no hash tables and some lock is present in the temp3 table. So, what happens now? Will the sql wait for the lock to get released on temp3 and only then it will insert into the temp1 or it will insert whatever data that it has got from the temp2 and then wait for the lock to get released on temp3?
November 1, 2009 at 1:01 am
The insert doesnt happen until it gets the data from both the tables. I just had an experiment on that.
But, one thing i wanted to understand is why is it putting the lock on the destination table when it is just selecting. I thought that the lock should be put on the destination table only after the select is completed and when the data is ready to insert.
Pramod
November 2, 2009 at 12:14 am
pramod_yg (11/1/2009)
But, one thing i wanted to understand is why is it putting the lock on the destination table when it is just selecting. I thought that the lock should be put on the destination table only after the select is completed and when the data is ready to insert.Pramod
Reason being sql server would not look at it as two discrete actions of select and insert. Its one statement and one TRANSACTION (implicit)
insert into #temp1select col2 from #temp2Union AllSelect col3 from #temp3
---------------------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply